一个交叉表的问题请教
有一表:
序号方向费用名称 金额日期
1收入系统维修收入￥90.002006-09-08
7支出机床支出 ￥89.002007-03-11
我用下面的语句:
ALTER PROCEDURE 存储过程1
AS
--查询处理
DECLARE @s varchar(8000)
--交叉报表处理代码头
SET @s= 'SELECT 日期 '
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+ ', '+QUOTENAME(费用名称)
+ '=SUM(CASE 费用名称 WHEN '+QUOTENAME(费用名称, ' ' ' ')
+ ' THEN 金额 END) '
FROM sz_vi所有收支
GROUP BY 费用名称
SELECT @s=@s
+ ', '+QUOTENAME(方向)
+N '=SUM(CASE 方向 WHEN '+QUOTENAME(方向,N ' ' ' ')
+N ' THEN 金额 END) '
FROM sz_vi所有收支
GROUP BY 方向
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+ '
FROM sz_vi所有收支
GROUP BY 日期 ')
出下面的果:
日期 机床支出系统维修收入 收入 支出
2006-09-08 ￥90.00 ￥90.00
2007-03-11 ￥89.00 ￥89.00
但我想要的效果是:
日期 机床支出系统维修收入 总收入 总支出 结余
2006-09-08 ￥90.00 ￥90.00 ￥90
2007-03-11 ￥89.00 ￥89.00 ￥89
合计: ￥89.00 ￥90.00 ￥90 ￥89.00 ￥1.00
上面的语句应如何修改!谢谢!
[解决办法]
ALTER
PROCEDURE 存储过程1
AS
--查询处理
DECLARE @s varchar(8000)
--交叉报表处理代码头
SET @s= 'SELECT 日期 '
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+ ', '+QUOTENAME(费用名称)
+ '=SUM(CASE 费用名称 WHEN '+QUOTENAME(费用名称, ' ' ' ')
+ ' THEN 金额 END) '
FROM sz_vi所有收支
GROUP BY 费用名称
SELECT @s=@s
+ ', '+QUOTENAME(方向)
+N '=SUM(CASE 方向 WHEN '+QUOTENAME(方向,N ' ' ' ')
+N ' THEN 金额 END) '
FROM sz_vi所有收支
GROUP BY 方向
--print @s+ ' INTO tmp_table_abcd FROM sz_vi所有收支 GROUP BY 日期 '
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+ ' INTO tmp_table_abcd FROM sz_vi所有收支 GROUP BY 日期 ')
SELECT @S = '
SELECT *, 结余= 收入-支出 FROM tmp_table_abcd
union all
SELECT 日期 = ' '合计 ' ', '
SELECT @S=@S + 'sum( ' + name + ') '+ ', ' from syscolumns
where id=(select top 1 id from sysobjects where name= 'tmp_table_abcd ' and xtype= 'U ') AND XTYPE=108
SELECT @S = @s + ' SUM(结余) from ( SELECT *, 结余= 收入-支出 FROM tmp_table_abcd) A '
--PRINT @S
EXEC(@S)
DROP TABLE tmp_table_abcd
[解决办法]
Create Table sz_vi所有收支
(序号Int,
方向Nvarchar(10),
费用名称Nvarchar(10),
金额Money,
日期DateTime)
Insert sz_vi所有收支 Select 1,N '收入 ',N '系统维修收入 ',90.00, '2006-09-08 '
Union All Select 7,N '支出 ',N '机床支出 ', 89.00, '2007-03-11 '
GO
CREATE PROCEDURE 存储过程1
AS
--查询处理
DECLARE @s Nvarchar(4000)
--交叉报表处理代码头
SET @s= ' '
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+ ', '+QUOTENAME(费用名称)
+N '=SUM(CASE 费用名称 WHEN N '+QUOTENAME(费用名称, ' ' ' ')
+N ' THEN 金额 ELSE 0 END) '
FROM sz_vi所有收支
GROUP BY 费用名称
SELECT @s=@s
+N '
, SUM(CASE 方向 WHEN N ' '收入 ' ' THEN 金额 ELSE 0 END) As 收入
, SUM(CASE 方向 WHEN N ' '支出 ' ' THEN 金额 ELSE 0 END) As 支出
, SUM(CASE 方向 WHEN N ' '收入 ' ' THEN 金额 WHEN N ' '支出 ' ' THEN - 金额 ELSE 0 END) As 结余
FROM sz_vi所有收支 '
--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(N 'SELECT Convert(Varchar(10), 日期, 120) As 日期 ' + @s+ N ' GROUP BY 日期 ' + ' Union All ' + N 'SELECT N ' '合计 ' ' ' + @s)
GO
EXEC 存储过程1
GO
Drop Table sz_vi所有收支
Drop PROCEDURE 存储过程1
/*
日期机床支出系统维修收入收入支出结余
2006-09-08.000090.000090.0000.000090.0000
2007-03-1189.0000.0000.000089.0000-89.0000
合计89.000090.000090.000089.00001.0000
*/
