GROUPING SETS/ROLLUP

CREATE Table Enployees  
(  
  [ID] INT IDENTITY(1,1) PRIMARY KEY ,  
  [NAME] NVARCHAR(100) ,  
  [GENDER] NVARCHAR(10),  
  [DEPARTMENT] NVARCHAR(3),  
  [SALARY] INT  
);  
GO

INSERT Enployees VALUES ('Name1', 'M', '1', 12000)  
INSERT Enployees VALUES ('Name2', 'M', '1', 24000)  
INSERT Enployees VALUES ('Name3', 'M', '2', 48000)  
INSERT Enployees VALUES ('Name4', 'F', '2', 12000)  
INSERT Enployees VALUES ('Name5', 'F', '3', 24000)  
INSERT Enployees VALUES ('Name6', 'F', '3', 48000)

SELECT GENDER  
             , DEPARTMENT  
             , SUM(SALARY) AS SALARY_TOTAL  
FROM Enployees  
GROUP BY GENDER, DEPARTMENT  
UNION  
SELECT GENDER  
       , null AS DEPARTMENT  
       , SUM(SALARY) AS SALARY_TOTAL  
FROM Enployees  
GROUP BY GENDER  
UNION  
SELECT null AS GENDER  
       , null AS DEPARTMENT  
       , SUM(SALARY) AS SALARY_TOTAL  
FROM Enployees

◎ GROUPING SETS的效能會比上面好很多

SELECT GENDER  
       , DEPARTMENT  
       , SUM(SALARY) AS SALARY_TOTAL  
FROM Enployees  
GROUP BY GROUPING SETS((GENDER,DEPARTMENT),  
                       (GENDER),  
                       ())

◎ ROLLUP的寫法又更簡潔一點

SELECT GENDER  
       , DEPARTMENT  
       , SUM(SALARY) AS SALARY_TOTAL  
       , GROUPING(GENDER) AS GENDER_PH--0代表是真實資料，1代表是個PLACEHOLDER  
       , GROUPING(DEPARTMENT) AS DEPARTMENT_PH--0代表是真實資料，1代表是個PLACEHOLDER  
FROM Enployees   
GROUP BY ROLLUP((GENDER),(DEPARTMENT))