是不是要ROLLUP的效果?看下面例子,是不是你要的
例如,简单表 Inventory 中包含:Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210 下列查询将生成小计报表:SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUPItem Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00 (7 row(s) affected)
例如,简单表 Inventory 中包含:Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210 下列查询将生成小计报表:SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUPItem Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00 (7 row(s) affected)
id1,id2,id3,num1,num2,num3,a,b,c,data
id 是汇总的依据,
num是要汇总的数据,
就是按三个id,依次汇总三个num数据,
id1是id2的总类,
id2是id3的总类。
a.b.c的值为计算得到的值,
其中id2汇总中的a值,
为同一次汇总记录中id1汇总的a的月初值,
其它的类似.
1 11 111 1 2 3 1 14 1
1 11 111 1 3 4 1 12 1
2 22 222 2 2 2 2 22 2之后:id1, id2, id3, num1, num2, num3, a, b, c, data
1 11 111 1 2 3 1 14 1
1 11 111 1 3 4 1 12 1
1 11 2 5 7 N M K
1 2 5 7 N M K
2 22 222 2 2 2 2 22 2
2 22 2 2 2 A B C
2 2 2 2 A B C