在使用前需要安装达梦数据库，并创建示例库DMHR

● ROLLUP ●

比如获取公司各城市每个部门员工人数分布和薪资分布情况，使用group by rollup的sql样例参考如下：

从结果集中可以看出group by rollup聚合了城市和部门组合，城市组合，所有组合三种情况。输出结果展示如下：

SELECTC.CITY_NAME                                                 ,DEPT.DEPARTMENT_NAME                                        ,COUNT(                       *) TOAL_PERSONNUM              ,ROUND((RATIO_TO_REPORT(COUNT(*))OVER())*100, 2) PERSON_RATIO,SUM(EMP.SALARY) TOTAL_SALARY                                ,ROUND((RATIO_TO_REPORT(SUM(EMP.SALARY))OVER())*100, 2) SAL_RATIO
FROMDMHR.EMPLOYEE EMP   ,DMHR.DEPARTMENT DEPT,DMHR.LOCATION L     ,DMHR.CITY C
WHEREEMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID(+)AND DEPT.LOCATION_ID  = L.LOCATION_ID(+)AND L.CITY_ID         = C.CITY_ID(+)
GROUP BYROLLUP (C.CITY_NAME, DEPT.DEPARTMENT_NAME);

结果集如下

从结果集中可以看出group by rollup聚合了城市和部门组合，城市组合，所有组合三种情况。输出结果展示如下：

● CUBE ●

上例中的sql将rollup换成cube，sql样例参考如下：

selectc.city_name                                                 ,dept.department_name                                        ,count(                       *) toal_personnum              ,round((ratio_to_report(count(*))over())*100, 2) person_ratio,sum(emp.salary) total_salary                                ,round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio
fromDMHR.employee emp   ,DMHR.department dept,DMHR.location l     ,DMHR.city c
whereemp.department_id = dept.department_id(+)and dept.location_id  = l.location_id(+)and l.city_id         = c.city_id(+)
group bycube (c.city_name, dept.department_name);

结果集如下：

 从结果集中可以看出group by cube聚合了城市和部门组合，城市组合，部门组合，所有组合四种情况。结果集展示如下：