文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Teradata语法迁移> DML> SELECT

SELECT

分享
更新时间: 2019/08/09 GMT+08:00

概述

Teradata的SELECT命令(缩写关键字为SEL)用于指定从哪一列中检索数据。

DWS中使用ANALYZE来收集优化器统计信息,这些统计信息将用于查询性能。

输入:ANALYZE和INSERT

INSERT INTO employee(empno,ename)  Values (1,'John');
COLLECT STAT on employee;

输出

INSERT INTO employee( empno, ename)                                            
SELECT 1 ,'John';
ANALYZE employee;

输入:ANALYZE和UPDATE

UPD employee SET ename = 'Jane'
        WHERE ename = 'John';
COLLECT STAT on employee;

输出

UPDATE employee SET ename = 'Jane'
 WHERE ename = 'John';
ANALYZE employee;

输入:ANALYZE和DELETE

DEL FROM employee WHERE ID > 10;
COLLECT STAT on employee;

输出

DELETE FROM employee WHERE ID > 10;
ANALYZE employee;

子句的顺序

从Teradata迁移SELECT语句时,各子句(FROM、WHERE、HAVING和GROUP BY)可按任意顺序排列。如果语句的FROM子句之前包含作为ALIAS的QUALIFY子句,则Migration Tool不会迁移该语句。

可以使用tdMigrateALIAS参数来配置ALIAS的迁移。

输入:子句的顺序

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 ;

输出

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 expr1 <> 'IC'
        AND expr2 <> 'IC'
        AND expr3 <> '';

输入:子句的顺序

SELECT
          TOP 10 *
     GROUP BY
          DeptNo
     WHERE
          empID < 100
FROM
          tbl_employee;

输出

SELECT
          *
     FROM
          tbl_employee
     WHERE
          empID < 100
     GROUP BY
          DeptNo LIMIT 10
;
说明:

如果输入脚本的FROM子句之前包含作为ALIAS的QUALIFY子句,Migration Tool将不会迁移该语句,也不会逐字复制输入的语句。

输入:在FROM子句之前,QUALIFY作为ALIAS的子句顺序

SELECT
          *
     FROM
          table1
     WHERE
          abc = (
               SELECT
                         col1 AS qualify
                    FROM
                         TABLE
                         WHERE
                              col1 = 5
          )
;

输出

SELECT
          *
     FROM
          table1
     WHERE
          abc = (
               SELECT
                         col1 AS qualify
                    FROM
                         TABLE
                         WHERE
                              col1 = 5
          )
;

扩展的Group By子句

如果用户希望数据库根据expr(s)的值对选定的行进行分组,则可指定GROUP BY子句。 如果此子句包含CUBE,ROLLUP或GROUPING SETS扩展,则除了常规分组之外,数据库还会生成超级聚合分组。这些特性在DWS中不可用,使用UNION ALL操作符可以实现类似的功能。

可以使用extendedGroupByClause参数来配置扩展GROUP BY子句的迁移。

可以使用tdMigrateALIAS参数来配置ALIAS的迁移。

输入:扩展的Group By子句 - CUBE

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 ;

输出

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 expr1 <> 'IC'
        AND expr2 <> 'IC'
        AND expr3 <> '';

输入:扩展的 Group By 子句 - ROLLUP

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);

输出

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

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);

输出

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
        );

TOP和SAMPLE子句

Teradata的TOP和SAMPLE子句在DWS中迁移为LIMIT。

  • TOP

    Migration Tool还支持迁移使用动态参数的TOP语句。

    说明:
    • 对于包含WITH TIES的TOP语句,需要指定ORDER BY子句,否则工具不会迁移该语句,只会原样复制。
    • 使用TOP和动态参数时:
      • 按照以下形式输入动态参数:
         TOP :<parameter_name>

        可使用的字符包括:小写英文字母(a-z)、大写英文字母(A-Z)、数字(0-9)、下划线(_)

    输入:SELECT...TOP

    SELECT TOP 1 c1, COUNT (*) cnt 
      FROM tab1 
     GROUP BY c1 
     ORDER BY cnt;	

    输出

    SELECT c1, COUNT( * ) cnt
      FROM tab1
     GROUP BY c1
     ORDER BY cnt 
     LIMIT 1;

    输入:SELECT...TOP PERCENT

    SELECT TOP 10 PERCENT c1, c2 
      FROM employee
     WHERE ...
     ORDER BY c2 DESC;

    输出

    WITH top_percent AS (
          SELECT c1, c2
            FROM employee
           WHERE ...
           ORDER BY c2 DESC
                        ) 
    SELECT *
      FROM top_percent 
     LIMIT (SELECT CEIL(COUNT( * ) * 10 / 100)
              FROM top_percent);

    输入:SELECT...TOP(指定动态参数

    SELECT
               TOP :Limit WITH TIES c1
              ,SUM (c2) sc2
         FROM
              tab1
         WHERE
              c3 > 10
         GROUP BY
              c1
         ORDER BY
              c1
    ;

    输出

    WITH top_ties AS (
         SELECT
                    c1
                   ,SUM (c2) sc2
                   ,rank (
                   ) OVER( ORDER BY c1 ) AS TOP_RNK
              FROM
                   tab1
              WHERE
                   c3 > 10
              GROUP BY
                   c1
    ) SELECT
              c1
              ,sc2
         FROM
              top_ties
         WHERE
              TOP_RNK <= :Limit
         ORDER BY
              TOP_RNK
    ;

    输入:SELECT...TOP指定动态参数和TIES

     SELECT
               TOP :Limit WITH TIES Customer_ID
       FROM
              Customer_t
       ORDER BY
              Customer_ID
    ;

    输出

    WITH top_ties AS (
         SELECT
                   Customer_ID
                   ,rank (
                   ) OVER( order by Customer_id) AS TOP_RNK
              FROM
                   Customer_t
    ) SELECT
              Customer_ID
         FROM
              top_ties
         WHERE
              TOP_RNK <= :Limit
         ORDER BY
              TOP_RNK
    ;

    输入:SELECT...TOP PERCENT(指定动态参数

    SELECT
              TOP :Input_Limit PERCENT WITH TIES c1
              ,SUM (c2) sc2
         FROM
              tab1
         GROUP BY
              c1
         ORDER BY
              c1
    ;

    输出

    WITH top_percent_ties AS (
         SELECT
                   c1
                   ,SUM (c2) sc2
                   ,rank (
                   ) OVER( ORDER BY c1 ) AS TOP_RNK
              FROM
                   tab1
              GROUP BY
                   c1
    ) SELECT
              c1
              ,sc2
         FROM
              top_percent_ties
         WHERE
              TOP_RNK <= (
                   SELECT
                             CEIL(COUNT( * ) * :Input_Limit / 100)
                        FROM
                             top_percent_ties
              )
         ORDER BY
              TOP_RNK
    ;
  • SAMPLE
    说明:

    工具仅支持在SAMPLE子句中使用单个正整数。

    输入:SELECT...SAMPLE

    SELECT c1, c2, c3 
      FROM tab1 
     WHERE c1 > 1000
    SAMPLE 1;

    输出

    SELECT c1, c2, c3
      FROM tab1
     WHERE c1 > 1000
     LIMIT 1;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区