其实db2关于group by 还有几个其他用法,x现将学习笔记贴出来:
group by 特殊说明:
select DEPARTMENT,Name,sum(amount) as sum,count(*) as count
from sale
inner join Employee on liaohaibing.EMPLOYEE.ID = liaohaibing.SALE.EMPLOYEEID
inner join liaohaibing.DEPARTMENT on
liaohaibing.DEPARTMENT.DEPARTMENTID = liaohaibing.EMPLOYEE.DEPARTMENTID
where liaohaibing.DEPARTMENT.DEPARTMENTID in(2,4,5,9)
group by rollup(DEPARTMENT,Name);
显示出来得结果如下表所示.
DEPARTMENT NAME SUM COUNT
184780 128
后勤部门 58554 48
开发部门 48625 32
资产管理部门 77601 48
后勤部门 fff 15033 16
后勤部门 ggg 18000 16
后勤部门 qqq 25521 16
开发部门 lhb 26293 16
开发部门 liaohaibing 22332 16
资产管理部门 ccc 28936 16
资产管理部门 ddd 28033 16
资产管理部门 eee 20632 16
Rollup
GROUP BY ROLLUP(DEPARTMENT,NAME)
第一是按把所有和显示到第一行。
第二是按GROUP BY DEPARTMENT 这种情况显示出来，这里是按部门进行分组。
第三是按GROUP BY DEPARTMENT,NAME情况显示出来，这里是按某部门中得名字进行分组得
还有个 参数:CUBE ,
例子: SELECT SALES_DATE, SALES_PERSON,
SUM(SALES) AS UNITS_SOLD
-- ,GROUPING(SALES_DATE) AS DATE_GROUP,
-- GROUPING(SALES_PERSON) AS SALES_GROUP
FROM SALES
GROUP BY cube (SALES_DATE, SALES_PERSON)
-- ORDER BY SALES_DATE, SALES_PERSON ;
Cube:
GROUP BY cube (SALES_DATE, SALES_PERSON)
第一是按把所有和显示到第一行。
第二是按 group by SALES_DATE 这种情况分组
第三是按group by sales_person这种情况分组
第四是按group by sales_date,sales_person分组.
另外一个参数为 group by sets
SELECT SALES_DATE, SALES_PERSON,
SUM(SALES) AS UNITS_SOLD
-- ,GROUPING(SALES_DATE) AS DATE_GROUP,
-- GROUPING(SALES_PERSON) AS SALES_GROUP
FROM SALES
GROUP BY GROUPING SETS (SALES_DATE, SALES_PERSON,())
-- ORDER BY SALES_DATE, SALES_PERSON ;
GROUPING SETS
GROUP BY GROUPING SETS (SALES_DATE, SALES_PERSON,())
第一是按把所有和显示到第一行
第二时候按 group by sales_date进行分组
第三是按group by sales_person进行分组.