SQL 合计问题 请帮忙 !!
我的SQL语句
SELECT 1 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, SOTH_XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHID, SOTH_XSY
UNION
SELECT 2 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHID
UNION
SELECT 3 AS XH,datepart(year,SOTH_YWRQ) as ND,SOTH_BMID, ' 'AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID
UNION
SELECT 4 AS XH,datepart(year,SOTH_YWRQ) as ND, ' 'AS SOTH_BMID, ' 'AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ)
ORDER BY datepart(year,SOTH_YWRQ), SOTH_KHID,SOTH_BMID, XH
希望先对XSY(销售员)进行合计 然后是客户(SOTH_KHID) 再是部门 最后是年度分别进行合计
但结果部门和年度的合计出现在最前,
请问应如何写才对 ?
谢谢!
[解决办法]
可以在 ' '的地方添加一些不用的字段以达到预计的排序效果，你看下面的这个可不可以， 不过使用了些不必要的字段。如果在程序设计中可以再将其替换。
下面是可以达到你想要的结果
SELECT 1 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, SOTH_XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE) JE, SUM(SOTHMX_YKPJE) YKPJE, SUM(SOTHMX_JE-SOTHMX_YKPJE) SOTHMX_YKPJE
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHID, SOTH_XSY
UNION
SELECT 2 AS XH,datepart(year,SOTH_YWRQ) as ND, SOTH_BMID, SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID, SOTH_KHID
UNION
SELECT 3 AS XH,datepart(year,SOTH_YWRQ) as ND,SOTH_BMID, 'KH空 'AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ),SOTH_BMID
UNION
SELECT 4 AS XH,datepart(year,SOTH_YWRQ) as ND, '空 ' AS SOTH_BMID, 'KH空 'AS SOTH_KHID, ' 'AS XSY,
CASE
WHEN SUM(SOTHMX_SFSL) <> 0 THEN SUM(SOTHMX_SFSL)
ELSE SUM(SOTHMX_YFSL) END AS XSSL,
SUM(SOTHMX_JE), SUM(SOTHMX_YKPJE), SUM(SOTHMX_JE-SOTHMX_YKPJE)
FROM SOTH, SOTHMX
WHERE SOTH_THDID=SOTHMX_THDID
GROUP BY datepart(year,SOTH_YWRQ) ORDER BY ND,SOTH_BMID,SOTH_KHID
1200610001 KH0001 陈勇 13.018600.015000.03600.0
1200610001 KH0001 王华 11.015600.014000.01600.0
2200610001 KH0001 24.034200.029000.05200.0
3200610001 KH空 24.034200.029000.05200.0
1200610002 KH0002 王浩 15.020800.020700.0100.0
2200610002 KH0002 15.020800.020700.0100.0
1200610002 KH0005 李巍 17.023200.021200.02000.0
2200610002 KH0005 17.023200.021200.02000.0
3200610002 KH空 32.044000.041900.02100.0
42006空 KH空 56.078200.070900.07300.0
