我的书签
添加书签
移除书签Oracle兼容-语法-PIVOT
PIVOT 语法可以将查询以交叉表的形式显示，与一般查询相比，交叉表具有更多的列和较少的行。
1. 语法
SELECT ...select_expr [, select_expr] ......[FROM table_references[PARTITION partition_list] [pivot_clause]][WHERE where_condition]...pivot_clause:PIVOT( aggregate_function ( expr ) [[AS] alias ][, aggregate_function ( expr ) [[AS] alias ] ]...pivot_for_clausepivot_in_clause)pivot_for_clause:FOR { column| ( column [, column]... )}pivot_in_clause:IN ({{ expr | ( expr [, expr]... ) } [ [ AS] alias]}...)
2. 定义和用法
pivot_clause定义了查询将在哪些字段上聚合数据。pivot_for_clause定义了哪些列将被分组，然后交叉聚合。pivot_in_clause用于过滤pivot_for_clause中列的值，子句中的每个值都将是一个单独的列，子句中使用的表达式只能是常量表达式。pivot_for_clause和pivot_in_clause没有使用到的列都将作为隐式分组的字段。select_expr只能使用隐式分组用到的字段以及pivot_in_clause中交叉聚合后生成的列。- 可以有多个
aggregate_function，但只允许有一个aggregate_function不指定别名，且aggregate_function不支持GROUP_CONCAT()和WM_CONCAT()函数。
3. Oracle兼容说明
- 不支持
XML with ANY和XML with Subquery语法
greatsql> CREATE TABLE t1(a INT, b INT, c INT);greatsql> CREATE TABLE t2(a INT, b INT, c INT);greatsql> SELECT * FROM t1 PIVOT XML (SUM(c) FOR a IN(ANY));ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'XML (SUM(c) FOR a IN(ANY))' at line 1greatsql> SELECT * FROM t1 PIVOT XML (SUM(c) FOR a IN(SELECT a FROM t2));ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'XML (SUM(c) FOR a IN(SELECT a FROM t2))' at line 1
- 不支持
table_references中的表结果有同名的列
-- 执行本示例需要先切换sql_mode = ORACLE，否则会报告语法错误，其他示例不受影响greatsql> SET sql_mode = ORACLE;greatsql> CREATE TABLE t1(a INT, b INT, c INT);greatsql> CREATE TABLE t2(a INT, b INT, c INT);greatsql> SELECT * FROM t1 JOIN t2 USING(a, c) PIVOT(SUM(c) FOR (a) IN(1, 2, 3, 4, 5 ,6));ERROR 1060 (42S21): Duplicate column name 'b'
4. 示例
-- 初始化测试数据greatsql> CREATE TABLE t1(a INT, b INT, c INT);greatsql> INSERT INTO t1 VALUES (1, 1, 1) (2, 1, 2), (3, 2, 1), (4, 2, 2), (5, 3, 1), (6, 3, 2);greatsql> SELECT * FROM t1 PIVOT(SUM(c) FOR(a) IN(1, 2, 3, 4, 5, 6)) ORDER BY b;+------+------+------+------+------+------+------+| b | 1 | 2 | 3 | 4 | 5 | 6 |+------+------+------+------+------+------+------+| 1 | 1 | 2 | NULL | NULL | NULL | NULL || 2 | NULL | NULL | 1 | 2 | NULL | NULL || 3 | NULL | NULL | NULL | NULL | 1 | 2 |+------+------+------+------+------+------+------+3 rows in set (0.00 sec)greatsql> SELECT * FROM t1 PIVOT(SUM(c) FOR(a) IN(1 AS I, 2 AS II, 3 AS III, 4 AS IV, 5 AS V, 6 AS VI)) ORDER BY b;+------+------+------+------+------+------+------+| b | I | II | III | IV | V | VI |+------+------+------+------+------+------+------+| 1 | 1 | 2 | NULL | NULL | NULL | NULL || 2 | NULL | NULL | 1 | 2 | NULL | NULL || 3 | NULL | NULL | NULL | NULL | 1 | 2 |+------+------+------+------+------+------+------+3 rows in set (0.00 sec)greatsql> SELECT * FROM t1 PIVOT(SUM(c) AS TOTAL FOR(a) IN(1 AS I, 2 AS II, 3 AS III, 4 AS IV, 5 AS V, 6 AS VI)) ORDER BY b;+------+---------+----------+-----------+----------+---------+----------+| b | I_TOTAL | II_TOTAL | III_TOTAL | IV_TOTAL | V_TOTAL | VI_TOTAL |+------+---------+----------+-----------+----------+---------+----------+| 1 | 1 | 2 | NULL | NULL | NULL | NULL || 2 | NULL | NULL | 1 | 2 | NULL | NULL || 3 | NULL | NULL | NULL | NULL | 1 | 2 |+------+---------+----------+-----------+----------+---------+----------+3 rows in set (0.00 sec)greatsql> SELECT * FROM t1 PIVOT(SUM(c) AS TOTAL, count(c) FOR(a) IN(1 AS I, 2 AS II, 3 AS III, 4 AS IV, 5 AS V, 6 AS VI)) ORDER BY b;+------+---------+---+----------+----+-----------+-----+----------+----+---------+---+----------+----+| b | I_TOTAL | I | II_TOTAL | II | III_TOTAL | III | IV_TOTAL | IV | V_TOTAL | V | VI_TOTAL | VI |+------+---------+---+----------+----+-----------+-----+----------+----+---------+---+----------+----+| 1 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 || 2 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 || 3 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 |+------+---------+---+----------+----+-----------+-----+----------+----+---------+---+----------+----+3 rows in set (0.00 sec)greatsql> SELECT * FROM t1 PIVOT(SUM(c) AS TOTAL, count(c) AS NUM FOR(a) IN(1 AS I, 2 AS II, 3 AS III, 4 AS IV, 5 AS V, 6 AS VI)) ORDER BY b;+------+---------+-------+----------+--------+-----------+---------+----------+--------+---------+-------+----------+--------+| b | I_TOTAL | I_NUM | II_TOTAL | II_NUM | III_TOTAL | III_NUM | IV_TOTAL | IV_NUM | V_TOTAL | V_NUM | VI_TOTAL | VI_NUM |+------+---------+-------+----------+--------+-----------+---------+----------+--------+---------+-------+----------+--------+| 1 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 || 2 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 || 3 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 |+------+---------+-------+----------+--------+-----------+---------+----------+--------+---------+-------+----------+--------+3 rows in set (0.00 sec)greatsql> CREATE TABLE t2(a INT, b INT, c INT, d INT);greatsql> INSERT INTO t2 VALUES (1, 1, 1, 1); (2, 1, 2, 2); (3, 2, 1, 1); (4, 2, 2, 2); (5, 3, 1, 1); (6, 3, 2, 2);greatsql> SELECT * FROM t2 PIVOT(SUM(c) FOR(a, d) IN((1,1), (2,2), (3,1), (4,2), (5,1), (6,2))) ORDER BY b;+------+------+------+------+------+------+------+| b | 1_1 | 2_2 | 3_1 | 4_2 | 5_1 | 6_2 |+------+------+------+------+------+------+------+| 1 | 1 | 2 | NULL | NULL | NULL | NULL || 2 | NULL | NULL | 1 | 2 | NULL | NULL || 3 | NULL | NULL | NULL | NULL | 1 | 2 |+------+------+------+------+------+------+------+3 rows in set (0.00 sec)
问题反馈
联系我们
扫码关注微信公众号
