标准语法:
SELECT语法的总体结构:
[ WITH with_subquery_table_name AS ( query ) ]
SELECT
[DISTINCT] select_expr [, select_expr ...]
[FROM table_reference [, ...] ]
[WHERE filter_condition]
[GROUP BY { expr | ROLLUP ( expr_list ) | CUBE ( expr_list ) | GROUPING SETS ( expr_list )} , ...]
[HAVING having_condition]
[ORDER BY {col_name | expr }
[ASC | DESC], ...]
[{ UNION [ ALL ] | INTERSECT | EXCEPT } (SELECT select_expr..)]
[LIMIT {row_count}]
1. WITH子句
WITH语句用于定义一个或者多个子查询,每个子查询定义一个临时表,类似于视图的定义; 在WITH中定义的临时表可以在当前查询的其他子句中引用;所有的WITH语句定义的临时表,都可以通过SELECT子句中的子查询定义来完成类似的效果,但是对于这些子查询或者临时表被后面的字句多次引用时,WITH语句只需要计算一次临时表结果,然后多次复用,从而达到减少公共表达式计算的次数。
语法:
[ WITH with_subquery [, ...] ]
而 with_suquery的语法为:
with_subquery_table_name AS ( query )
参数:
SELECT [ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]
FROM table_reference [, ...]
with_subquery_table_name [ [ AS ] alias ]
table_name [ * ] [ [ AS ] alias ]
( subquery ) [ AS ] alias
table_reference join_type table_reference
[ ON join_condition ]
[WHERE filter_condition]
GROUP BY [ROLLUP | CUBE] expression [, ...]
[ HAVING condition ]
[ ORDER BY expression
[ ASC | DESC ]
[ LIMIT { count | ALL } ]
query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query
select * from t1
union
select * from t2
except
select * from t3
order by c1;
select * from t1
union
select * from t2
intersect
select * from t3
order by c1;
select * from t1
union
(select * from t2
intersect
select * from t3)
order by c1;
-- 一般的使用方式
select id, long_test from test start with id < 100 connect by prior id = long_test
-- 整体做子查询
select * from (select id, long_test from test start with id in (1,2,3) connect by prior id = long_test) as hier order by 1,2
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。