查询报表加汇总请教

[乖喵咪]

select Sum(S3.Khgs) KHS,Sum(S1.RKSL) RKS,Sum(S2.CCGS) CCS,TM.CX,(Case When Sum(S3.Khgs) =0 Then 0 else Sum(S2.CCGS)/Sum(S3.Khgs)  end)*100 XL,TD3.JHXL,(Case When Sum(S6.Khgs2) =0 Then 0 else Sum(S5.CCGS2)/Sum(S6.Khgs2)  end)*100 XLL

From ZZSCRB TM
LEFT JOIN 
(
   Select Sum(TD2.RKSL) as RKSL,TM.DocEntry
   From ZZSCRB TM 
   LEFT JOIN ZZSCRBA TD2 on TD2.DocEntry=TM.DocEntry
   Where TM.RKRQ>=@DateFr  and TM.RKRQ<=@DateTo 
   Group by TM.DocEntry
) S1 on S1.DocEntry=TM.DocEntry

LEFT JOIN 
(
   Select Sum(TD2.CCGS) as CCGS,TM.DocEntry
   From ZZSCRB TM 
   LEFT JOIN ZZSCRBA TD2 on TD2.DocEntry=TM.DocEntry
   Where TM.RKRQ>=@DateFr  and TM.RKRQ<=@DateTo 
   Group by TM.DocEntry
) S2 on S2.DocEntry=TM.DocEntry
LEFT JOIN 
(
   Select Sum(TD2.GS) as Khgs,TM.DocEntry
   From ZZSCRB TM on T.CalDate=TM.RKRQ
   LEFT JOIN ZZSCRBB TD2 on TD2.DocEntry=TM.DocEntry
   Where TM.RKRQ>=@DateFr  and TM.RKRQ<=@DateTo 
   Group by TM.DocEntry
) S3 on S3.DocEntry=TM.DocEntry

LEFT JOIN 
(
   Select Sum(TD2.RKSL) as RKSL2,TM.DocEntry
   From ZZSCRB TM 
   LEFT JOIN ZZSCRBA TD2 on TD2.DocEntry=TM.DocEntry
   Where TM.RKRQ=@RKRQTO
   Group by TM.DocEntry
) S4 on S4.DocEntry=TM.DocEntry

LEFT JOIN 
(
   Select Sum(TD2.CCGS) as CCGS2,TM.DocEntry
   From ZZSCRB TM 
   LEFT JOIN ZZSCRBA TD2 on TD2.DocEntry=TM.DocEntry
   Where TM.RKRQ=@RKRQTO
   Group by TM.DocEntry
) S5 on S5.DocEntry=TM.DocEntry
LEFT JOIN 
(
   Select Sum(TD2.GS) as Khgs2,TM.DocEntry
   From ZZSCRB TM on T.CalDate=TM.RKRQ
   LEFT JOIN ZZSCRBB TD2 on TD2.DocEntry=TM.DocEntry
   Where TM.RKRQ=@RKRQTO
   Group by TM.DocEntry
) S6 on S6.DocEntry=TM.DocEntry

FUll JOIN ZZSCXLA TD3 on TD3.CX=TM.CX and TD3.JHRQ=CONVERT(varchar(6) ,TM.RKRQ(), 112 )

Where TM.RKRQ >= @RKRQFR and TM.RKRQ <= @RKRQTO and
     TM.CX=@CX  and TM.DocStatus='O'

Group by TM.CX,TD3.JHXL

如下图，我需要在尾部加一行，汇总所有产线的产出工时和考核工时，然后总效率等于产出工时/考核工时，应该怎么写？

[卖水果的net]

楼主研究一下 group by rollup。

[乖喵咪]

已经搞定了，我直接用union all连接就好了，虽然我刚刚接触这个东西，但是觉得蛮有意思的。

引用 2 楼 ch21st 的回复:

1.你脚本可以再精简下，查询数据集表相通的可以合并成一个 2.如果加汇总，用rollup就行 简单的示例：

;WITH t(k,v) AS(
   SELECT 'A',100 UNION ALL
   SELECT 'A',200 UNION ALL
   SELECT 'B',300 UNION ALL
   SELECT 'B',400 UNION ALL
   SELECT 'C',500 
)
SELECT ISNULL(t.k,'Summary') as K,SUM(t.v) AS s_v 
FROM t 
GROUP BY t.k
WITH ROLLUP

k	s_v
A	300
B	700
C	500
S	1500

[道素]

1.你脚本可以再精简下，查询数据集表相通的可以合并成一个 2.如果加汇总，用rollup就行 简单的示例：

;WITH t(k,v) AS(
   SELECT 'A',100 UNION ALL
   SELECT 'A',200 UNION ALL
   SELECT 'B',300 UNION ALL
   SELECT 'B',400 UNION ALL
   SELECT 'C',500 
)
SELECT ISNULL(t.k,'Summary') as K,SUM(t.v) AS s_v 
FROM t 
GROUP BY t.k
WITH ROLLUP

k	s_v
A	300
B	700
C	500
S	1500

[乖喵咪]

以前没接触过SQL,最近一个多月才开始用，WITH ROLLUP，是不是还要在我之前写的语句外再套一层？

引用 2 楼 ch21st 的回复:

1.你脚本可以再精简下，查询数据集表相通的可以合并成一个 2.如果加汇总，用rollup就行 简单的示例：

;WITH t(k,v) AS(
   SELECT 'A',100 UNION ALL
   SELECT 'A',200 UNION ALL
   SELECT 'B',300 UNION ALL
   SELECT 'B',400 UNION ALL
   SELECT 'C',500 
)
SELECT ISNULL(t.k,'Summary') as K,SUM(t.v) AS s_v 
FROM t 
GROUP BY t.k
WITH ROLLUP

k	s_v
A	300
B	700
C	500
S	1500

[道素]

引用 4 楼 sielsn 的回复:

已经搞定了，我直接用union all连接就好了，虽然我刚刚接触这个东西，但是觉得蛮有意思的。 [quote=引用 2 楼 ch21st 的回复:] 1.你脚本可以再精简下，查询数据集表相通的可以合并成一个 2.如果加汇总，用rollup就行 简单的示例：

;WITH t(k,v) AS(
   SELECT 'A',100 UNION ALL
   SELECT 'A',200 UNION ALL
   SELECT 'B',300 UNION ALL
   SELECT 'B',400 UNION ALL
   SELECT 'C',500 
)
SELECT ISNULL(t.k,'Summary') as K,SUM(t.v) AS s_v 
FROM t 
GROUP BY t.k
WITH ROLLUP

k	s_v
A	300
B	700
C	500
S	1500

[/quote] union all 其实就是变成两个查询了，相当于统计两次，另外rollup不仅仅是合计，还同时可以计算小计，如果的汇总比较细，用union all 就不好处理了，比如下面的例子分三级进行统计

;WITH tb(class1,class2,class3,data_value)AS(
   SELECT 'a','a1','a11',10 UNION ALL
   SELECT 'a','a1','a11',90 UNION ALL
   SELECT 'a','a1','a12',20 UNION ALL
   SELECT 'a','a2','a21',30 UNION ALL
   SELECT 'a','a2','a21',80 UNION ALL
   SELECT 'a','a2','a22',40 UNION ALL
   SELECT 'b','b1','b11',50 UNION ALL
   SELECT 'b','b1','b11',150 UNION ALL
   SELECT 'b','b1','b12',60 UNION ALL
   SELECT 'b','b2','b21',70 UNION ALL
   SELECT 'b','b2','b21',50 UNION ALL
   SELECT 'c','c1','c11',80 
   
)
SELECT CASE WHEN GROUPING(tb.class1)+GROUPING(tb.class2)+GROUPING(tb.class3)=3 THEN 'Summary' ELSE tb.class1 END  AS Class1
      ,CASE WHEN GROUPING(tb.class2)+GROUPING(tb.class3)=2 THEN tb.class1+' Summary' ELSE tb.class2 END AS Class2
      ,CASE WHEN GROUPING(tb.class3)=1 THEN tb.class1+'-'+tb.class2+ ' summary' ELSE tb.class3 END AS Class3
      ,SUM(data_value) AS data_value
FROM tb GROUP BY tb.class1,tb.class2,tb.class3 WITH ROLLUP

+---------+-----------+--------------+------------+
| Class1  | Class2    | Class3       | data_value |
+---------+-----------+--------------+------------+
| a       | a1        | a11          | 100        |
| a       | a1        | a12          | 20         |
| a       | a1        | a-a1 summary | 120        |
| a       | a2        | a21          | 110        |
| a       | a2        | a22          | 40         |
| a       | a2        | a-a2 summary | 150        |
| a       | a Summary | NULL         | 270        |
| b       | b1        | b11          | 200        |
| b       | b1        | b12          | 60         |
| b       | b1        | b-b1 summary | 260        |
| b       | b2        | b21          | 120        |
| b       | b2        | b-b2 summary | 120        |
| b       | b Summary | NULL         | 380        |
| c       | c1        | c11          | 80         |
| c       | c1        | c-c1 summary | 80         |
| c       | c Summary | NULL         | 80         |
| Summary | NULL      | NULL         | 730        |
+---------+-----------+--------------+------------+