交叉报表问题，急!!!! 望赐教

[lyrqh]

两个表
business(收支录入)（id,name,money,user_id,date）
expendses（收支类别）(id,name,remark,type)
expenses中的id（收支类别编码,vchar(6)）是business中的name(vchar(6)）的外键
expenses 中的type（char(1)） 代表该收支类别是收入还是支出


数据如下：
business:
id name money date
1 000001 1000 2004.9.2
2 000002 9550 2004.9.2
3 000003 5450 2004.9.2

expenses:

id name remark type
000001 运费 .. 1
000002 电费 .. 1 （1代表支出）
000003 收营业款 .. 0 （0代表收入）


现要用一张交叉表统计business 中的数据

按type 分组（既 按收入支出分组）
再按收支类别编码expenses.id分组

要求得到如下数据：

2004.9.2 crosstabtotal
0（收入） 20000 20000
0（收入合计） 20000.00 20000
1（支出） 9550 9550
5450 5450
1（支出合计） 15000.00 15000
毛利 5000.00 5000.00

[lyrqh]

xlhl(顽皮兔子)
语句是这样，没错。
但求出的是收入和支出的合计数，我要收入-支出=？

[lyrqh]

xlhl(顽皮兔子)
语句是这样，没错。
但求出的是收入和支出的合计数，我要收入-支出=？
并且要按收支项目分组

[luotitan]

如果数据库是SQL SERVER ，你可以参考一下SQL SERVER 帮助中交叉数据报表的SQL脚本，稍微改一下就可以了。

[luotitan]

访问和更改关系数据


交叉数据报表
有时候需要旋转结果以便在水平方向显示列，而在垂直方向显示行。这就是所谓的创建 PivotTable®、创建交叉数据报表或旋转数据。

假定有一个表 Pivot，其中每季度占一行。对 Pivot 的 SELECT 操作在垂直方向上列出这些季度：

Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

生成报表的表必须是这样的，其中每年占一行，每个季度的数值显示在一个单独的列中，如：

Year
Q1
Q2
Q3
Q4

1990
1.1
1.2
1.3
1.4

1991
2.1
2.2
2.3
2.4



下面的语句用于创建 Pivot 表并在其中填入第一个表中的数据：

USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

下面是用于创建旋转结果的 SELECT 语句：

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

该 SELECT 语句还处理其中每个季度占多行的表。GROUP BY 语句将 Pivot 中一年的所有行合并成一行输出。当执行分组操作时，SUM 聚合中的 CASE 函数的应用方式是这样的：将每季度的 Amount 值添加到结果集的适当列中，在其它季度的结果集列中添加 0。

如果该 SELECT 语句的结果用作电子表格的输入，那么电子表格将很容易计算每年的合计。当从应用程序使用 SELECT 时，可能更易于增强 SELECT 语句来计算每年的合计。例如：

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO

带有 CUBE 的 GROUP BY 和带有 ROLLUP 的 GROUP BY 都计算与本例显示相同的信息种类，但格式稍有不同。


---------------------------------------------------------------------------------
希望你能看明白，个人觉得比较简单了。

[xlhl]

建立一个视图
create view v_report as
select business.date,
case expendses.type when 0 then business.money else business.money end as money ,
case expendses.type when 0 then '收入' else '支出' end as type
from business inner join expendses
on (expendses.id=business.id)
----------------------------------
rows:type
columns:date
values:money
设置money，及计算列的format #,##0.00;#,##0.00
这样正数，负数显示的都是一样了。
合计数=收入-支出
结果因该是这样
--------------------------------------
2004.9.2 crosstabtotal
收入 20000 20000
收入合计 20000.00 20000
支出 9550 9550
支出 5450 5450
支出合计 15000.00 15000
毛利 5000.00 5000.00
---------------------------------------------------
没有设置format因该是这样
2004.9.2 crosstabtotal
收入 20000 20000
收入合计 20000.00 20000
支出 -9550 -9550
支出 -5450 -5450
支出合计 -15000.00 -15000
毛利 5000.00 5000.00

[lyrqh]

自己顶，高手都去哪了？？？？？

[lyrqh]

加分了

[xlhl]

SQL语句如下：
select business.date,business.money,expendses.type
from business,expendses
where expendses.id=business.id
----------------------------------
rows:type
columns:date
values:money

[peixun]

不太清楚，应该不难，

[lyrqh]

非常感谢 xlhl(顽皮兔子)及 luotitan(泰坦) 给予的帮助，谢谢啦！！！

xlhl(顽皮兔子)提供的view 经过修改如下：
CREATE VIEW "DBA"."v_report2" ( /* view-column-name, ... */ )
AS
SELECT "business"."date","business"."name",case expenses.type when '1' then business.money*-1 else business.money end as money,case expenses.type when '0' then '收入' else '支出' end as type
FROM business inner join expenses on (expenses.id=business.name);

主要是在算支出时用了 business.money * -1 不知这样用可不可以，但报表是对的，望指正

luotitan(泰坦)提供了详细的思路，xlhl(顽皮兔子)提供了详细的解决办法，
各给50分

[lyrqh]

aaa