594
社区成员
发帖
与我相关
我的任务
分享
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
;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 |
+---------+-----------+--------------+------------+
;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