向--dawugui-->求助：统计数量的事情？

Question

SELECT (A.XCHD962200CS_NUM+A.BMFWCS_NUM+A.SQZYZCS_NUM+A.ZHHDCS_NUM) AS H_A_N
, (A.XCHD962200CJRS_NUM+A.BMFWCJRS_NUM+A.SQZYZCJRS_NUM+A.ZHHDCJRS_NUM) AS HS_A_N,  A.* FROM REPORT_QSJZHDTJ_ALL_NUM a
得到如下结果集合：
ZONE_N  ST_NUM  H_A_N   HS_A_N  C1      C11     C2      C21     C3      C31     C4      C41    
闵行区 10 9 67 3 11 2 23 1 11 3 22
杨浦区 12 8 129 2 21 2 33 2 22 2 53
然后我想得到如下总计结果：
ZONE_N  ST_NUM  H_A_N   HS_A_N  C1      C11     C2      C21     C3      C31     C4      C41    
闵行区 10 9 67 3 11 2 23 1 11 3 22
杨浦区 12 8 129 2 21 2 33 2 22 2 53
总计：   22      17      196     5       32      4       55      3       33      5       78
如何写sql语句呢？

Answer 1

用union all
应该可以
SELECT (A.XCHD962200CS_NUM+A.BMFWCS_NUM+A.SQZYZCS_NUM+A.ZHHDCS_NUM)AS H_A_N,(A.XCHD962200CJRS_NUM+A.BMFWCJRS_NUM+A.SQZYZCJRS_NUM+A.ZHHDCJRS_NUM) AS HS_A_N,A.* FROM REPORT_QSJZHDTJ_ALL_NUM a 
union all
select sum(),sum(),........................

Answer 2

在group by 后面加with rollup就行了,就是那么简单...

Answer 3

用UNION ALL再加上SUM()来解决吧！SELECT (A.XCHD962200CS_NUM+A.BMFWCS_NUM+A.SQZYZCS_NUM+A.ZHHDCS_NUM) AS HDCS_ALL_NUM, (A.XCHD962200CJRS_NUM+A.BMFWCJRS_NUM+A.SQZYZCJRS_NUM+A.ZHHDCJRS_NUM) AS HDCJRS_ALL_NUM,  A.* 
FROM REPORT_QSJZHDTJ_ALL_NUM a 
UNION ALL
SELECT SUM(HDCS_ALL_NUM),SUM(HDCJRS_ALL_NUM),'总计：',
    SUM(STREET_NUM),SUM(XCHD962200CS_NUM),SUM(XCHD962200CJRS_NUM),
    SUM(BMFWCS_NUM),SUM(BMFWCJRS_NUM),SUM(SQZYZCS_NUM),SUM(SQZYZCJRS_NUM),SUM(ZHHDCS_NUM),SUM(ZHHDCJRS_NUM) 
FROM (
SELECT   (A.XCHD962200CS_NUM+A.BMFWCS_NUM+A.SQZYZCS_NUM+A.ZHHDCS_NUM) AS HDCS_ALL_NUM,       (A.XCHD962200CJRS_NUM+A.BMFWCJRS_NUM+A.SQZYZCJRS_NUM+A.ZHHDCJRS_NUM) AS HDCJRS_ALL_NUM,  A.* 
    FROM REPORT_QSJZHDTJ_ALL_NUM a 
) C

Answer 4

用这个可以实现，但是sql也太长了些，有别的方法没有啊？

Answer 5

那要写多少个group by 啊？

Answer 6

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)