扩展Group By子句
如果用户希望数据库根据expr(s)的值对选定的行进行分组,则可指定GROUP BY子句。 如果此子句包含CUBE,ROLLUP或GROUPING SETS扩展,则除了常规分组之外,数据库还会生成超级聚合分组。这些特性在GaussDB(DWS)中不可用,使用UNION ALL操作符可以实现类似的功能。
可以使用extendedGroupByClause参数来配置扩展GROUP BY子句的迁移。
输入:扩展Group By子句,使用CUBE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT expr1 AS alias1 , expr2 AS alias2 , expr3 AS alias3 , MAX( expr4 ), ... FROM tab1 T1 INNER JOIN tab2 T2 ON T1.c1 = T2.c2 ... AND T3.c5 = '010' AND ... WHERE T1.c7 = '000' AND ... HAVING alias1 <> 'IC' AND alias2 <> 'IC' AND alias3 <> '' GROUP BY 1, 2, 3 ; |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT expr1 AS "alias1" ,expr2 AS "alias2" ,expr3 AS "alias3" ,MAX( expr4 ) ,... FROM tab1 T1 INNER JOIN tab2 T2 ON T1.c1 = T2.c2 ... AND T3.c5 = '010' AND ... WHERE T1.c7 = '000' AND ... GROUP BY 1 ,2 ,3 HAVING alias1 <> 'IC' AND alias2 <> 'IC' AND alias3 <> '' ; |
输入:扩展Group By子句,使用ROLLUP
1 2 3 4 5 |
SELECT d.dname, e.job, MAX(e.sal) FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno WHERE e.job IS NOT NULL GROUP BY ROLLUP (d.dname, e.job); |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT dname, job, ColumnAlias1 FROM ( SELECT MAX(e.sal) AS ColumnAlias1, d.dname, e.job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY d.dname ,e.job UNION ALL SELECT MAX(e.sal) AS ColumnAlias1, d.dname, NULL AS job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY d.dname UNION ALL SELECT MAX( e.sal ) AS ColumnAlias1, NULL AS dname, NULL AS job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL ); |
输入:扩展Group By子句,使用GROUPING SETS
1 2 3 4 5 |
SELECT d.dname, e.job, MAX(e.sal) FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno WHERE e.job IS NOT NULL GROUP BY GROUPING SETS(d.dname, e.job); |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT dname, job, ColumnAlias1 FROM ( SELECT MAX(e.sal) AS ColumnAlias1 , d.dname, NULL AS job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY d.dname UNION ALL SELECT MAX(e.sal) AS ColumnAlias1 , NULL AS dname, e.job FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno WHERE e.job IS NOT NULL GROUP BY e.job ); |