1 建表

CREATE TABLE [dbo].[c_account](

[id] [bigint] IDENTITY(1,1) NOT NULL,

[acc_date] [datetime] NULL DEFAULT (getdate()),

[acc_inorout] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,

[acc_sorts] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,

[acc_sum] [money] NULL,

[acc_detail] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,

[acc_didian] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL,

[acc_remark] [nvarchar](100) COLLATE Chinese_PRC_CI_AS NULL)

2 向表中添加数据

3 查询

--收支表 按时间、分类统计

declare @sql varchar(8000);

set @sql='';

select @sql=@sql+[acc_sorts]+'=sum(case when [acc_sorts] ='''+[acc_sorts]+''' then convert(numeric(18,2),[acc_sum]) else 0 end),' from (select distinct [acc_sorts] from c_account) a;

set @sql=left(@sql,len(@sql)-1); set @sql='select max(substring(CONVeRT(varchar(12) , acc_date, 23 ),1,7)) 年月,max(acc_inorout) 收支,convert(numeric(18,2),sum([acc_sum])) 总计,'+@sql+' from c_account group by substring(CONVeRT(varchar(12) , acc_date, 23 ),1,7),acc_inorout order by substring(CONVeRT(varchar(12) , acc_date, 23 ),1,7),acc_inorout';

print @sql;

exec(@sql);

--收支表 按时间、分类统计 详细

declare @sql varchar(8000),@sql1 varchar(8000);

set @sql=''; select @sql=@sql+[acc_sorts]+'=sum(case when [acc_sorts] ='''+[acc_sorts]+''' then convert(numeric(18,2),[acc_sum])else 0 end),' from (select distinct [acc_sorts] from c_account) a;

set @sql=left(@sql,len(@sql)-1);

set @sql1='select * from (';

select @sql1=@sql1+'select max(substring(CONVeRT(varchar(12) , acc_date, 23 ),1,7)) 年月,max(acc_inorout)+''+'' 收支,convert(numeric(18,2),sum([acc_sum])) 总计,'+@sql+' from c_account group by substring(CONVeRT(varchar(12) , acc_date, 23 ),1,7),acc_inorout ';

select @sql1=@sql1+'union all(select CONVeRT(varchar(12) , acc_date, 23 ) 年月,max(acc_inorout) 收支,convert(numeric(18,2),sum([acc_sum])) 总计,'+@sql+' from c_account group by CONVeRT(varchar(12) , acc_date, 23 ),acc_inorout) ';

select @sql1=@sql1+') r order by substring(r.年月,1,7),r.收支,r.总计'

print @sql1;

exec(@sql1);