SELECT¶
说明¶
在数据库中进行数据检索
语法¶
select ::=
top_option ::=
group_by_clause ::=
having_clause ::=
union_clause ::=
order_by_clause ::=
nulls_option ::=
numerical_expr ::=
table_reference ::=
pivot_clause ::=
unpivot_clause ::=
table_ref_table ::=
table_ref_query ::=
table_ref_join ::=
condition_expr ::=
partition_clause ::=
参数¶
numerical_expr
数值常量或者结果值为数值的数学表达式，如果为数值常量，则常量可以不带括号，如果为数学表达式，则表达式必须位于括号内。
c_alias
输出列的别名。若指定了别名，则在查询结果中对应列的名称就为指定的别名，且该别名可以在GROUP BY子句和ORDER BY子句中被引用，但不能在WHERE子句和HAVING子句中被引用。
expr
列名或表达式
select_flashback
具体用法参考 SQL命令列表 中的 SELECT FLASHBACK模块
join_condition
连接条件
aggregate_function
表示聚集函数，现在后台支持简单的聚集函数包括：MAX MIN AVG COUNT SUM VARIANCE STDDEV MEDIAN LISTAGG
注解
SELECT语句用于数据检索。对于SELECT语句中的各个子句，从逻辑上可以认为系统将按如下的顺序进行处理：
- 处理FROM子句，得到FROM子句中指定的所有数据源联接的结果；
处理WHERE子句。对输入根据条件condition进行筛选，返回那些满足条件的行；
处理GROUP BY子句。将输入按分组属性进行分组；
处理HAVING子句，过滤掉那些不满足条件的组；
处理ORDER BY子句，按输入进行排序；
处理SETOP子句，将输入与另一个查询的结果进行集合操作；
处理LIMIT和TOP子句，过滤掉那些不在指定范围内的元组；
处理SELECT子句，对输入进行投影操作，即只保留在SELECT子句指定要返回的列，而舍弃其余的列；
若语句中包含INTO子句，则将查询结果插入到新创建的表中，否则，将查询结果返回给用户。
注意FOR UPDATE子句并没有处理在上述的处理流程中，这是由于FOR UPDATE子句并不影响查询结果，而只是告诉执行器要对查询结果所涉及的行加排它锁（在通常情况下加的是共享锁）。
SELECT语句是SQL中最复杂的语句之一，本节只说明SELECT语句中SELECT子句的用法。 对于其它子句，请参见 SELECT: INTO , SELECT: FROM , SELECT: WHERE , SELECT: SETOP , SELECT: GROUP BY , SELECT: HAVING , SELECT: ORDER BY , SELECT: LIMIT SELECT: TOP PERCENT , SELECT: FOR UPDATE 等主题获得详细描述。在说明各子句时使用一个由STUDENT, COURSE, ELECT等三个表构成的通用示例，请参见 SELECT 获得对通用示例的详细描述。
SELECT子句用于指定输出列，一个SELECT语句最多可以输出65535个列。
示例¶
示例1: 选择所有列
-- 清理环境
DROP TABLE tab1 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab1 (a INT, b INT, c INT);
INSERT INTO tab1 VALUES(1, 10, 900);
INSERT INTO tab1 VALUES(2, 20, 500);
INSERT INTO tab1 VALUES(2, NULL, 200);
INSERT INTO tab1 VALUES(3, 30, 1000);
INSERT INTO tab1 VALUES(3, 40, 800);
INSERT INTO tab1 VALUES(3, 10, 600);
-- 选择所有列
SELECT * FROM tab1 ORDER BY a, b, c;
A(int) |B(int) |C(int) |
------------------------------------
1 |10 |900 |
------------------------------------
2 |20 |500 |
------------------------------------
2 |null |200 |
------------------------------------
3 |10 |600 |
------------------------------------
3 |30 |1000 |
------------------------------------
3 |40 |800 |
总数目：6
-- 删除表
DROP TABLE tab1;
示例2: 指定要返回的列
-- 清理环境
DROP TABLE tab2 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab2 (a INT, b INT, c INT);
INSERT INTO tab2 VALUES(1, 10, 900);
INSERT INTO tab2 VALUES(2, 20, 500);
INSERT INTO tab2 VALUES(2, NULL, 200);
INSERT INTO tab2 VALUES(3, 30, 1000);
INSERT INTO tab2 VALUES(3, 40, 800);
INSERT INTO tab2 VALUES(3, 10, 600);
-- 选择所有列
SELECT c, a FROM tab2 ORDER BY a, c;
C(int) |A(int) |
------------------------
900 |1 |
------------------------
200 |2 |
------------------------
500 |2 |
------------------------
600 |3 |
------------------------
800 |3 |
------------------------
1000 |3 |
总数目：6
-- 删除表
DROP TABLE tab2;
示例3: 查询中有重名列
-- 清理环境
DROP TABLE tab3 CASCADE;
DROP TABLE tab4 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab3 (a INT, b INT, c INT);
INSERT INTO tab3 VALUES(1, 10, 900);
INSERT INTO tab3 VALUES(2, 20, 500);
INSERT INTO tab3 VALUES(2, NULL, 200);
INSERT INTO tab3 VALUES(3, 30, 1000);
INSERT INTO tab3 VALUES(3, 40, 800);
INSERT INTO tab3 VALUES(3, 10, 600);
CREATE TABLE tab4 (a INT, b INT, c INT);
INSERT INTO tab4 VALUES(1, 310, 2900);
INSERT INTO tab4 VALUES(2, 220, 21500);
INSERT INTO tab4 VALUES(3, 360, 71000);
-- 查询中有重名列 —— 列明前面带上表名
SELECT tab3.c FROM tab3, tab4 WHERE tab3.a = tab4.a ORDER BY tab3.c;
C(int) |
------------
200 |
------------
500 |
------------
600 |
------------
800 |
------------
900 |
------------
1000 |
总数目：6
SELECT tab4.c FROM tab3, tab4 WHERE tab3.a = tab4.a ORDER BY tab4.c;
C(int) |
------------
2900 |
------------
21500 |
------------
21500 |
------------
71000 |
------------
71000 |
------------
71000 |
总数目：6
-- 删除表
DROP TABLE tab3;
DROP TABLE tab4;
示例4: 消除结果中的重复行
-- 清理环境
DROP TABLE tab4 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab4 (a INT, b INT, c INT);
INSERT INTO tab4 VALUES(1, 310, 2900);
INSERT INTO tab4 VALUES(1, 310, 2900);
INSERT INTO tab4 VALUES(2, 220, 21500);
INSERT INTO tab4 VALUES(3, 360, 71000);
INSERT INTO tab4 VALUES(3, 360, 71000);
-- 消除结果中的重复行
SELECT DISTINCT * FROM tab4 ORDER BY a, b, c;
A(int) |B(int) |C(int) |
------------------------------------
1 |310 |2900 |
------------------------------------
2 |220 |21500 |
------------------------------------
3 |360 |71000 |
总数目：3
-- 删除表
DROP TABLE tab4;
示例5: 多表连接查询
-- 清理环境
DROP TABLE tab5_1 CASCADE;
DROP TABLE tab5_2 CASCADE;
-- 创建表并插入数据
CREATE TABLE tab5_1 (a1 INT, b1 INT, c INT);
CREATE TABLE tab5_2 (a2 INT, b2 INT, c INT);
INSERT INTO tab5_1 VALUES(1, 10, 100);
INSERT INTO tab5_1 VALUES(2, 20, 200);
INSERT INTO tab5_1 VALUES(3, 30, 300);
INSERT INTO tab5_1 VALUES(4, 40, 400);
INSERT INTO tab5_2 VALUES(1, 10, 100);
INSERT INTO tab5_2 VALUES(3, 30, 300);
INSERT INTO tab5_2 VALUES(5, 50, 500);
INSERT INTO tab5_2 VALUES(7, 70, 700);
-- 自连接 inner join 与 join 效果相同 inner可以缺省
SELECT * FROM tab5_1 INNER JOIN tab5_2 ON tab5_1.a1 = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
总数目：2
SELECT * FROM tab5_1 JOIN tab5_2 ON tab5_1.a1 = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
总数目：2
SELECT * FROM tab5_1,tab5_2 WHERE tab5_1.a1 = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
总数目：2
SELECT * FROM tab5_1 INNER JOIN tab5_2 USING(c) ORDER BY c, a1, b1, a2, b2;
C(int) |A1(int) |B1(int) |A2(int) |B2(int) |
----------------------------------------------------------------
100 |1 |10 |1 |10 |
----------------------------------------------------------------
300 |3 |30 |3 |30 |
总数目：2
-- 外连接 left/right/full outer join 中的 outer 可以缺省
-- 左外连接
SELECT * FROM tab5_1 LEFT OUTER JOIN tab5_2 ON tab5_1.a1 = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
2 |20 |200 |null |null |null |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
4 |40 |400 |null |null |null |
总数目：4
SELECT * FROM tab5_1 LEFT JOIN tab5_2 ON tab5_1.a1 = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
2 |20 |200 |null |null |null |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
4 |40 |400 |null |null |null |
总数目：4
-- where 后的条件表达式的(+)在哪一边，这边当不满足条件时，就为 null
SELECT * FROM tab5_1, tAB5_2 where tab5_1.a1 = tab5_2.a2(+) ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
2 |20 |200 |null |null |null |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
4 |40 |400 |null |null |null |
总数目：4
SELECT * FROM tab5_2 LEFT JOIN tab5_1 ON tab5_1.a1 = tab5_2.a2 ORDER BY a2, b2, tab5_2.c, a1, b1, tab5_1.c;
A2(int) |B2(int) |C(int) |A1(int) |B1(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
5 |50 |500 |null |null |null |
----------------------------------------------------------------------------
7 |70 |700 |null |null |null |
总数目：4
SELECT * FROM tab5_2 NATURAL LEFT JOIN tab5_1 ORDER BY a2, b2, tab5_2.c, a1, b1, tab5_1.c;
C(int) |A2(int) |B2(int) |A1(int) |B1(int) |
----------------------------------------------------------------
100 |1 |10 |1 |10 |
----------------------------------------------------------------
300 |3 |30 |3 |30 |
----------------------------------------------------------------
500 |5 |50 |null |null |
----------------------------------------------------------------
700 |7 |70 |null |null |
总数目：4
-- 右外连接
SELECT * FROM tab5_1 RIGHT JOIN tab5_2 ON tab5_1.a1 = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
null |null |null |5 |50 |500 |
----------------------------------------------------------------------------
null |null |null |7 |70 |700 |
总数目：4
SELECT * FROM tab5_1, tab5_2 where tab5_1.a1(+) = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
null |null |null |5 |50 |500 |
----------------------------------------------------------------------------
null |null |null |7 |70 |700 |
总数目：4
SELECT * FROM tab5_2 RIGHT JOIN tab5_1 ON tab5_1.a1 = tab5_2.a2 ORDER BY a2, b2, tab5_2.c, a1, b1, tab5_1.c;
A2(int) |B2(int) |C(int) |A1(int) |B1(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
null |null |null |2 |20 |200 |
----------------------------------------------------------------------------
null |null |null |4 |40 |400 |
总数目：4
SELECT * FROM tab5_2 NATURAL RIGHT JOIN tab5_1 ORDER BY a2, b2, tab5_2.c, a1, b1, tab5_1.c;
C(int) |A2(int) |B2(int) |A1(int) |B1(int) |
----------------------------------------------------------------
100 |1 |10 |1 |10 |
----------------------------------------------------------------
300 |3 |30 |3 |30 |
----------------------------------------------------------------
200 |null |null |2 |20 |
----------------------------------------------------------------
400 |null |null |4 |40 |
总数目：4
-- 全连接
SELECT * FROM tab5_1 FULL JOIN tab5_2 ON tab5_1.a1 = tab5_2.a2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
2 |20 |200 |null |null |null |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
4 |40 |400 |null |null |null |
----------------------------------------------------------------------------
null |null |null |5 |50 |500 |
----------------------------------------------------------------------------
null |null |null |7 |70 |700 |
总数目：6
SELECT * FROM tab5_2 FULL JOIN tab5_1 ON tab5_1.a1 = tab5_2.a2 ORDER BY a2, b2, tab5_2.c, a1, b1, tab5_1.c;
A2(int) |B2(int) |C(int) |A1(int) |B1(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
5 |50 |500 |null |null |null |
----------------------------------------------------------------------------
7 |70 |700 |null |null |null |
----------------------------------------------------------------------------
null |null |null |2 |20 |200 |
----------------------------------------------------------------------------
null |null |null |4 |40 |400 |
总数目：6
SELECT * FROM tab5_2 NATURAL FULL JOIN tab5_1 ORDER BY a2, b2, tab5_2.c, a1, b1, tab5_1.c;
C(int) |A2(int) |B2(int) |A1(int) |B1(int) |
----------------------------------------------------------------
100 |1 |10 |1 |10 |
----------------------------------------------------------------
300 |3 |30 |3 |30 |
----------------------------------------------------------------
500 |5 |50 |null |null |
----------------------------------------------------------------
700 |7 |70 |null |null |
----------------------------------------------------------------
200 |null |null |2 |20 |
----------------------------------------------------------------
400 |null |null |4 |40 |
总数目：6
-- 笛卡尔乘积
SELECT * FROM tab5_1 CROSS JOIN tab5_2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
1 |10 |100 |3 |30 |300 |
----------------------------------------------------------------------------
1 |10 |100 |5 |50 |500 |
----------------------------------------------------------------------------
1 |10 |100 |7 |70 |700 |
----------------------------------------------------------------------------
2 |20 |200 |1 |10 |100 |
----------------------------------------------------------------------------
2 |20 |200 |3 |30 |300 |
----------------------------------------------------------------------------
2 |20 |200 |5 |50 |500 |
----------------------------------------------------------------------------
2 |20 |200 |7 |70 |700 |
----------------------------------------------------------------------------
3 |30 |300 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
3 |30 |300 |5 |50 |500 |
----------------------------------------------------------------------------
3 |30 |300 |7 |70 |700 |
----------------------------------------------------------------------------
4 |40 |400 |1 |10 |100 |
----------------------------------------------------------------------------
4 |40 |400 |3 |30 |300 |
----------------------------------------------------------------------------
4 |40 |400 |5 |50 |500 |
----------------------------------------------------------------------------
4 |40 |400 |7 |70 |700 |
总数目：16
SELECT * FROM tab5_1, tab5_2 ORDER BY a1, b1, tab5_1.c, a2, b2, tab5_2.c;
A1(int) |B1(int) |C(int) |A2(int) |B2(int) |C(int) |
----------------------------------------------------------------------------
1 |10 |100 |1 |10 |100 |
----------------------------------------------------------------------------
1 |10 |100 |3 |30 |300 |
----------------------------------------------------------------------------
1 |10 |100 |5 |50 |500 |
----------------------------------------------------------------------------
1 |10 |100 |7 |70 |700 |
----------------------------------------------------------------------------
2 |20 |200 |1 |10 |100 |
----------------------------------------------------------------------------
2 |20 |200 |3 |30 |300 |
----------------------------------------------------------------------------
2 |20 |200 |5 |50 |500 |
----------------------------------------------------------------------------
2 |20 |200 |7 |70 |700 |
----------------------------------------------------------------------------
3 |30 |300 |1 |10 |100 |
----------------------------------------------------------------------------
3 |30 |300 |3 |30 |300 |
----------------------------------------------------------------------------
3 |30 |300 |5 |50 |500 |
----------------------------------------------------------------------------
3 |30 |300 |7 |70 |700 |
----------------------------------------------------------------------------
4 |40 |400 |1 |10 |100 |
----------------------------------------------------------------------------
4 |40 |400 |3 |30 |300 |
----------------------------------------------------------------------------
4 |40 |400 |5 |50 |500 |
----------------------------------------------------------------------------
4 |40 |400 |7 |70 |700 |
总数目：16
-- 自然连接
SELECT * FROM tab5_1 NATURAL JOIN tab5_2 ORDER BY c, a1, b1, a2, b2;
C(int) |A1(int) |B1(int) |A2(int) |B2(int) |
----------------------------------------------------------------
100 |1 |10 |1 |10 |
----------------------------------------------------------------
300 |3 |30 |3 |30 |
总数目：2
-- 带上 on 或者 using 关键字，会报语法错误
SELECT * FROM tab5_1 NATURAL JOIN tab5_2 USING(c) ORDER BY c, a1, b1, a2, b2;
ERROR, Common column name "C" appears more than once in right table
SELECT * FROM tab5_1 NATURAL JOIN tab5_2 ON tab5_1.a1 = tab5_2.a2 ORDER BY c, a1, b1, a2, b2;
ERROR, parser: syntax error at or near "tab5_1" at character 89
-- 删除表
DROP TABLE tab5_1;
DROP TABLE tab5_2;
示例6：行列转换
--pivot行转列功能
drop table tab6_1 cascade;
create table tab6_1(id int ,name varchar(100) null,course varchar(100) null,score int,teacher varchar(100),teachtime int,credit int);
insert into tab6_1 values(11,'张三','Chinese',40,'张老师',45,5);
insert into tab6_1 values(11,'张三','Math',80,'李老师',60,10);
insert into tab6_1 values(11,'张三','English',60,'王老师',50,3);
insert into tab6_1 values(12,'李四','Chinese',60,'张老师',45,5);
insert into tab6_1 values(12,'李四', 'Math',100,'李老师',60,10);
insert into tab6_1 values(12,'李四', 'English',50,'王老师',50,3);
insert into tab6_1 values(11,'张三', 'Chinese',95,'张老师',30,5);
--单列（course）列转置
select * from tab6_1 pivot(sum(score) for course in('Chinese', 'Math','English'))order by id,teachtime;
ID(int) |NAME(varchar) |TEACHER(varchar) |TEACHTIME(int) |CREDIT(int) |'Chinese'(bigint) |'Math'(bigint) |'English'(bigint) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |张三 |张老师 |30 |5 |95 |null |null |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |张三 |张老师 |45 |5 |40 |null |null |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |张三 |王老师 |50 |3 |null |null |60 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |张三 |李老师 |60 |10 |null |80 |null |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
12 |李四 |张老师 |45 |5 |60 |null |null |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
12 |李四 |王老师 |50 |3 |null |null |50 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
12 |李四 |李老师 |60 |10 |null |100 |null |
总数目：7
--多列（course,teacher）两列转置
select * from tab6_1 pivot(max(score) "最高分数",sum(teachtime)"学时" for(course,teacher)in (('Chinese','张老师') "语文",('Math','李老师')"数学",('English','王老师')"英语"))order by id,credit;
ID(int) |NAME(varchar) |CREDIT(int) |语文_最高分数(int) |语文_学时(bigint) |数学_最高分数(int) |数学_学时(bigint) |英语_最高分数(int) |英语_学时(bigint) |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |张三 |3 |null |null |null |null |60 |50 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |张三 |5 |95 |75 |null |null |null |null |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
11 |张三 |10 |null |null |80 |60 |null |null |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12 |李四 |3 |null |null |null |null |50 |50 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12 |李四 |5 |60 |45 |null |null |null |null |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12 |李四 |10 |null |null |100 |60 |null |null |
总数目：6
--unpivot列转行功能
drop table tab6_2 cascade;
create table tab6_2(id int,name varchar(100),Chinese int,Math int,English int,C_teacher varchar(20),M_teacher varchar(20),E_teacher varchar(20));
insert into tab6_2 values(1,'张三',99,88,77,'张老师','李老师','王老师');
insert into tab6_2 values(2,'李四',66,55,null,'张老师','李老师','王老师');
--查询结果包含空字段记录
select * from tab6_2 unpivot include nulls(score for course in(Chinese,Math,English)) order by id,course;
ID(int) |NAME(varchar) |C_TEACHER(varchar) |M_TEACHER(varchar) |E_TEACHER(varchar) |COURSE(varchar) |SCORE(int) |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |张三 |张老师 |李老师 |王老师 |CHINESE |99 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |张三 |张老师 |李老师 |王老师 |ENGLISH |77 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |张三 |张老师 |李老师 |王老师 |MATH |88 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |李四 |张老师 |李老师 |王老师 |CHINESE |66 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |李四 |张老师 |李老师 |王老师 |ENGLISH |null |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |李四 |张老师 |李老师 |王老师 |MATH |55 |
总数目：6
--查询结果不包含空字段记录
select * from tab6_2 unpivot exclude nulls(score for course in(Chinese,Math,English)) order by id,course;
ID(int) |NAME(varchar) |C_TEACHER(varchar) |M_TEACHER(varchar) |E_TEACHER(varchar) |COURSE(varchar) |SCORE(int) |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |张三 |张老师 |李老师 |王老师 |CHINESE |99 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |张三 |张老师 |李老师 |王老师 |ENGLISH |77 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |张三 |张老师 |李老师 |王老师 |MATH |88 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |李四 |张老师 |李老师 |王老师 |CHINESE |66 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |李四 |张老师 |李老师 |王老师 |MATH |55 |
总数目：5
--多列转置测试
select * from tab6_2 unpivot ((score,tname) for course in((Chinese,C_teacher)as 'Chinese',(Math,M_teacher)as 'Math',(English,E_teacher)as 'English')) order by id,course;
ID(int) |NAME(varchar) |COURSE(varchar) |SCORE(int) |TNAME(varchar) |
-----------------------------------------------------------------------------------------
1 |张三 |Chinese |99 |张老师 |
-----------------------------------------------------------------------------------------
1 |张三 |English |77 |王老师 |
-----------------------------------------------------------------------------------------
1 |张三 |Math |88 |李老师 |
-----------------------------------------------------------------------------------------
2 |李四 |Chinese |66 |张老师 |
-----------------------------------------------------------------------------------------
2 |李四 |English |null |王老师 |
-----------------------------------------------------------------------------------------
2 |李四 |Math |55 |李老师 |
总数目：6
--pivot/unpivot相互转换
select * from(select * from tab6_2 unpivot ((score) for (course) in(Chinese ,Math,English)))pivot(sum(score) for course in('CHINESE' ,'MATH','ENGLISH')) order by id;
ID(int) |NAME(varchar) |C_TEACHER(varchar) |M_TEACHER(varchar) |E_TEACHER(varchar) |'CHINESE' (bigint) |'MATH'(bigint) |'ENGLISH'(bigint) |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |张三 |张老师 |李老师 |王老师 |99 |88 |77 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 |李四 |张老师 |李老师 |王老师 |66 |55 |null |
总数目：2
drop table t;
create table t(id int ,name varchar(100) null,course varchar(100) null,score int);
insert into t values(11,'zs','Chinese',40);
insert into t values(11,'zs','Math',80);
insert into t values(11,'zs','English',60);
insert into t values(12,'ls','Chinese',60);
insert into t values(12,'ls', 'Math',100);
insert into t values(12,'ls', 'English',50);
insert into t values(11,'zs', 'Chinese',140);
select * from (select * from t pivot(sum(score)for course in('Chinese','Math','English'))) unpivot (score for course in ("'Chinese'","'Math'","'English'")) order by id,course;
ID(int) |NAME(varchar) |COURSE(varchar) |SCORE(bigint) |
------------------------------------------------------------------------
11 |zs |'Chinese' |180 |
------------------------------------------------------------------------
11 |zs |'English' |60 |
------------------------------------------------------------------------
11 |zs |'Math' |80 |
------------------------------------------------------------------------
12 |ls |'Chinese' |60 |
------------------------------------------------------------------------
12 |ls |'English' |50 |
------------------------------------------------------------------------
12 |ls |'Math' |100 |
总数目：6
drop table t;
drop table tab6_1 cascade;
drop table tab6_2 cascade;