SQL 交叉查询
ID DDBH COLOR1 Amount1 COLOR2 Amount2 COLOR3 Amount3
19T33338红色75.00白色150.00绿色 100.00
20T33338棕色30.00黑色60.00紫色 500.00
21T33338青色30.00蓝色60.00灰色 20.00
22T33338灰色15.00棕色30.00红色 40.00
上面是 数据
如何用SQL 语句查出 所有颜色分类的 amount1,amount2,amount3 的总和
结果如:
红色115.00 白色 150.00 绿色 100.00 棕色60.00 黑色 60.00 紫色 500.00 青色 30.00 蓝色 60.00 灰色 35.00
[解决办法]
SELECT A.COLOR,ISNULL(SUM(QTY),0) AS QTY
FROM (
SELECT ISNULL(COLOR1, ' ') AS COLOR,ISNULL(SUM(AMOUNT1),0)AS QTY
FROM COLOR
GROUP BY COLOR1
UNION ALL
SELECT ISNULL(COLOR2, ' ') AS COLOR,ISNULL(SUM(AMOUNT2),0)AS QTY
FROM COLOR
GROUP BY COLOR2
UNION ALL
SELECT ISNULL(COLOR3, ' ') AS COLOR,ISNULL(SUM(AMOUNT3),0)AS QTY
FROM COLOR
GROUP BY COLOR3
) A
GROUP BY A.COLOR
