一个交叉表的问题请教

Question

有一表:
序号 方向 费用名称           金额 日期
1 收入 系统维修收入 ￥90.00 2006-09-08
7 支出 机床支出          ￥89.00 2007-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
上面的语句应如何修改!谢谢!

Answer 1

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=108SELECT @S = @s + ' SUM(结余) from ( SELECT *, 结余= 收入-支出  FROM tmp_table_abcd) A '--PRINT @S
EXEC(@S)DROP TABLE tmp_table_abcd

Answer 2

补充一下建表的语句，这个很重要create table sz_vi所有收支 (序号 int, 方向 varchar(20), 费用名称  varchar(50), 金额 numeric(10,2), 日期 varchar(20))

Answer 3

谢谢
xhc_ozh(孤帆远影)朋友:
我用了你的语句出现下面的提示:警告: 聚合或其它 SET 操作消除了空值。
（所影响的行数为 3 行）
服务器: 消息 205，级别 16，状态 1，行 2
包含 UNION 运算符的 SQL 语句中的所有查询都必须在目标列表中具有相同数目的表达式。
存储过程: gskdata.dbo.存储过程3
返回代码 = 0

Answer 4

你的“方向”是不是固定只有那麼幾種？如果不是的話，你的“結余”是怎麼計算的？
如果是的話，那一段就可以不用動態語句了。xhc_ozh(孤帆远影) 的代碼中是將“方向”固定為只有“收入”“支出”這兩種情況計算的。

Answer 5

“存储过程1”里面所用的一些代码和数据表的结构有很大的关系，表[sz_vi所有收支]的结构以及里面的收支类型数量不同的话也“可能”需要对存储过程进行修正，楼主只给了很少的数据并且没有给出数据表的结构，所以这个存储过程只能作为参考用，楼主需要根据实际情况修正楼主可以将存储过程里的两个print注释取消，然后看看输出的语句有什么问题，或是贴上来看看也行--这是我所用的数据表和数据，存储过程也是基于这些数据来测试的
create table sz_vi所有收支 (序号 int, 方向 varchar(20), 费用名称  varchar(50), 金额 numeric(10,2), 日期 varchar(20))
insert into sz_vi所有收支 
select '1', '收入系统维修', '收入', '90.00', '2006-09-08'
union select '7', '支出机床', '支出', '89.00', '2007-03-11'
union select '5', '收入系统维修', '收入', '40.00', '2007-03-11'
union select '6', '支出机床', '支出', '63.00', '2006-09-08'
union select '4', '支出机床2', '支出', '63.00', '2006-09-08'--drop table sz_vi所有收支
--运行结果
日期           收入         支出   收入系统维修  支出机床  支出机床2      结余 
------------ ---------- ---------- ------------- --------- ---------- ---------
2006-09-08   90.00      126.00     90.00         63.00     63.00      -36.00
2007-03-11   40.00      89.00      40.00         89.00     NULL       -49.00
合计         130.00     215.00     130.00        152.00    63.00      -85.00

Answer 6

多谢:xhc_ozh(孤帆远影) 朋友那么细心,我的数据跟你一样,只不过就是日期的数据类型是:datetime奇怪:
我直接用你的在语句,是可以得出结果,但如果一改为日期型,就不可以了.

Answer 7

xhc_ozh(孤帆远影) 朋友,能不能再帮我改善一下,我试了,OK,但是如果数据中还没有支出的话,会报错.

Answer 8

可以不用像xhc_ozh(孤帆远影) 那麼複雜，借助臨時表來實現，可以直接在你的語句基礎上稍加修改即可實現。另外，日期的数据类型是datetime也沒有問題，一樣可以實現的。

Answer 9

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 .0000 90.0000 90.0000 .0000 90.0000
2007-03-11 89.0000 .0000 .0000 89.0000 -89.0000
合计 89.0000 90.0000 90.0000 89.0000 1.0000
*/

Answer 10

在你的數據庫裡，你執行這段代碼ALTER 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

Answer 11

历害呀!非常感谢xhc_ozh(孤帆远影)和paoluo(一天到晚游泳的鱼)两位朋友,帮了大忙呀,我还要继续努力学习才行呀!
这就是水平呀!!!
真的非常感谢两位.

Answer 12

再有个问题请教：
我想加个条件，但是出错：
ALTER PROCEDURE 存储过程9
(
@begin datetime,@end datetime
)
AS
DECLARE @s varchar(8000)
SET @s= ''
SELECT @s=@s
+','+QUOTENAME(费用名称)
+'=SUM(CASE 费用名称 WHEN '+QUOTENAME(费用名称,'''')
+' THEN 金额 ELSE 0 END)'
FROM sz_vi所有收支 where (日期 BETWEEN '''+convert(char(10),@begin,120)+''' AND '''+convert(char(10),@end,120) +''')       
         GROUP BY 费用名称
SELECT @s=@s
+'
, SUM(CASE 方向 WHEN ''收入'' THEN 金额 ELSE 0 END) As 收入
, SUM(CASE 方向 WHEN ''支出'' THEN 金额 ELSE 0 END) As 支出
, SUM(CASE 方向 WHEN ''收入'' THEN 金额 WHEN ''支出'' THEN - 金额 ELSE 0 END) As 结余
FROM sz_vi所有收支 where (日期 BETWEEN '''+convert(char(10),@begin,120)+''' AND '''+convert(char(10),@end,120) +''')'
EXEC('SELECT Convert(Varchar(10), 日期, 120) As 日期' + @s+ ' GROUP BY 日期' + ' Union All ' + 'SELECT ''合计''' + @s) 

Answer 13

ALTER PROCEDURE 存储过程9
(
@begin datetime,@end datetime
)
AS
DECLARE @s varchar(8000)
SET @s= ''
SELECT @s=@s
+','+QUOTENAME(费用名称)
+'=SUM(CASE 费用名称 WHEN '+QUOTENAME(费用名称,'''')
+' THEN 金额 ELSE 0 END)'
FROM sz_vi所有收支 where (日期 BETWEEN @begin AND @end  --只有這一處修改
         GROUP BY 费用名称
SELECT @s=@s
+'
, SUM(CASE 方向 WHEN ''收入'' THEN 金额 ELSE 0 END) As 收入
, SUM(CASE 方向 WHEN ''支出'' THEN 金额 ELSE 0 END) As 支出
, SUM(CASE 方向 WHEN ''收入'' THEN 金额 WHEN ''支出'' THEN - 金额 ELSE 0 END) As 结余
FROM sz_vi所有收支 where (日期 BETWEEN '''+convert(char(10),@begin,120)+''' AND '''+convert(char(10),@end,120) +''')'
EXEC('SELECT Convert(Varchar(10), 日期, 120) As 日期' + @s+ ' GROUP BY 日期' + ' Union All ' + 'SELECT ''合计''' + @s) 
GO