CodePudding user response:

CodePudding user response:

 


The CREATE TABLE # T 
(DT VARCHAR (10), 
A VARCHAR (10), 
INT B, 
C INT, 
D INT) 

INSERT INTO # T 
SELECT '2020070600', 'A', 10, 30 UNION ALL 
SELECT '2020070600', 'B', 100200300 UNION ALL 
SELECT '2020070600', 'C', 1000200, 0300 UNION ALL 
SELECT '2020070600', 'B', 20300500 UNION ALL 
SELECT '2020070606', 'A', 101201301 UNION ALL 
SELECT '2020070606', 'AA', 102202302 UNION ALL 
SELECT '2020070606', 'B', 103203303 UNION ALL 
SELECT '2020070606', 'A', 100200300, 

SELECT * FROM # T ORDER BY DT 

SELECT the CASE WHEN the GROUPING (A)=1 AND GROUPING (DT)=0 THEN DT 
WHEN GROUPING (A)=0 AND GROUPING (DT)=0 THEN A 
The ELSE 'total' END AS LABEL, 
The SUM (B) AS TOTAL_B, 
The SUM (C) AS TOTAL_C, 
The SUM (D) AS TOTAL_D 
The FROM # T 
GROUP BY DT, WITH A CUBE 
HAVING NOT (GROUPING (DT)=1 AND GROUPING (A)=0) 
The ORDER BY GROUPING (DT), DT, GROUPING DESC (A), A 


SELECT the CASE WHEN the GROUPING (A)=1 THEN 'total' ELSE END AS A LABEL, 
AS the SUM (B) B, 
The SUM (C) AS C, 
The SUM (D) AS D 
The FROM # T 
GROUP BY A WITH CUBE