GROUPING SETS/CUBE(排列組合)

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
       , DEPARTMENT
       , SUM(SALARY) AS SALARY_TOTAL
FROM Enployees
GROUP BY DEPARTMENT
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
       , GROUPING(GENDER) AS GENDER_PH--0代表是真實資料，1代表是個PLACEHOLDER
       , GROUPING(DEPARTMENT) AS DEPARTMENT_PH--0代表是真實資料，1代表是個PLACEHOLDER
FROM Enployees
GROUP BY GROUPING SETS((GENDER,DEPARTMENT),--第一組GROUP BY
                       (GENDER),--第二組GROUP BY
                       (DEPARTMENT),--第三組GROUP BY
                       ())

◎ CUBE的寫法又更簡潔一點

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