# SELECT

#### 概述

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

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

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

 ```1 2 3``` ```UPD employee SET ename = 'Jane' WHERE ename = 'John'; COLLECT STAT on employee; ```

 ```1 2 3``` ```UPDATE employee SET ename = 'Jane' WHERE ename = 'John'; ANALYZE employee; ```

 ```1 2``` ```DEL FROM employee WHERE ID > 10; COLLECT STAT on employee; ```

 ```1 2``` ```DELETE FROM employee WHERE ID > 10; ANALYZE employee; ```

#### 子句的顺序

 ``` 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``` ```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 <> ''; ```

 ```1 2 3 4 5 6 7 8``` ```SELECT TOP 10 * GROUP BY DeptNo WHERE empID < 100 FROM tbl_employee; ```

 ```1 2 3 4 5 6 7 8 9``` ```SELECT * FROM tbl_employee WHERE empID < 100 GROUP BY DeptNo LIMIT 10 ; ```

 ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14``` ```SELECT * FROM table1 WHERE abc = ( SELECT col1 AS qualify FROM TABLE WHERE col1 = 5 ) ; ```

 ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14``` ```SELECT * FROM table1 WHERE abc = ( SELECT col1 AS qualify FROM TABLE WHERE col1 = 5 ) ; ```

#### 扩展的Group By子句

 ``` 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``` ```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 <> ''; ```

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

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

#### TOP和SAMPLE子句

• TOP

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

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

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

输入：SELECT...TOP

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

输出

 ```1 2 3 4 5``` ```SELECT c1, COUNT( * ) cnt FROM tab1 GROUP BY c1 ORDER BY cnt LIMIT 1; ```

输入：SELECT...TOP PERCENT

 ```1 2 3 4``` ```SELECT TOP 10 PERCENT c1, c2 FROM employee WHERE ... ORDER BY c2 DESC; ```

输出

 ``` 1 2 3 4 5 6 7 8 9 10``` ```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（指定动态参数

 ``` 1 2 3 4 5 6 7 8 9 10 11 12``` ```SELECT TOP :Limit WITH TIES c1 ,SUM (c2) sc2 FROM tab1 WHERE c3 > 10 GROUP BY c1 ORDER BY c1 ; ```

输出

 ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22``` ```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

 ```1 2 3 4 5 6 7``` ``` SELECT TOP :Limit WITH TIES Customer_ID FROM Customer_t ORDER BY Customer_ID ; ```

输出

 ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16``` ```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（指定动态参数

 ``` 1 2 3 4 5 6 7 8 9 10``` ```SELECT TOP :Input_Limit PERCENT WITH TIES c1 ,SUM (c2) sc2 FROM tab1 GROUP BY c1 ORDER BY c1 ; ```

输出

 ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25``` ```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

 ```1 2 3 4``` ```SELECT c1, c2, c3 FROM tab1 WHERE c1 > 1000 SAMPLE 1; ```

输出

 ```1 2 3 4``` ```SELECT c1, c2, c3 FROM tab1 WHERE c1 > 1000 LIMIT 1; ```

*必选