文档首页> 数据仓库服务 GaussDB(DWS)> 工具指南> DSC> SQL语法迁移参考> Teradata语法迁移> 数据操作语句（DML）> SELECT> 扩展Group By子句
更新时间：2024-04-18 GMT+08:00
查看PDF
分享

扩展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
        );
父主题： SELECT
分享：

    相关文档

    相关产品