我的书签
添加书签
移除书签Oracle兼容-语法-PIVOT
PIVOT 语法可以将查询以交叉表的形式显示，与一般查询相比，交叉表具有更多的列和较少的行。
1. 语法
1. SELECT ...
2. select_expr [, select_expr] ...
3. ...
4. [FROM table_references
5. [PARTITION partition_list] [pivot_clause]]
6. [WHERE where_condition]
7. ...
9. pivot_clause:
10. PIVOT
11. ( aggregate_function ( expr ) [[AS] alias ]
12. [, aggregate_function ( expr ) [[AS] alias ] ]...
13. pivot_for_clause
14. pivot_in_clause
15. )
17. pivot_for_clause:
18. FOR { column
19. | ( column [, column]... )
20. }
22. pivot_in_clause:
23. IN ({
24. { expr | ( expr [, expr]... ) } [ [ AS] alias]
25. }...
26. )
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语法
1. greatsql> CREATE TABLE t1(a INT, b INT, c INT);
2. greatsql> CREATE TABLE t2(a INT, b INT, c INT);
3. greatsql> SELECT * FROM t1 PIVOT XML (SUM(c) FOR a IN(ANY));
4. 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 1
6. greatsql> SELECT * FROM t1 PIVOT XML (SUM(c) FOR a IN(SELECT a FROM t2));
7. 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中的表结果有同名的列
1. -- 执行本示例需要先切换sql_mode = ORACLE，否则会报告语法错误，其他示例不受影响
2. greatsql> SET sql_mode = ORACLE;
3. greatsql> CREATE TABLE t1(a INT, b INT, c INT);
4. greatsql> CREATE TABLE t2(a INT, b INT, c INT);
5. greatsql> SELECT * FROM t1 JOIN t2 USING(a, c) PIVOT(SUM(c) FOR (a) IN(1, 2, 3, 4, 5 ,6));
6. ERROR 1060 (42S21): Duplicate column name 'b'
4. 示例
1. -- 初始化测试数据
2. greatsql> CREATE TABLE t1(a INT, b INT, c INT);
3. greatsql> INSERT INTO t1 VALUES (1, 1, 1) (2, 1, 2), (3, 2, 1), (4, 2, 2), (5, 3, 1), (6, 3, 2);
5. greatsql> SELECT * FROM t1 PIVOT(SUM(c) FOR(a) IN(1, 2, 3, 4, 5, 6)) ORDER BY b;
6. +------+------+------+------+------+------+------+
7. | b | 1 | 2 | 3 | 4 | 5 | 6 |
8. +------+------+------+------+------+------+------+
9. | 1 | 1 | 2 | NULL | NULL | NULL | NULL |
10. | 2 | NULL | NULL | 1 | 2 | NULL | NULL |
11. | 3 | NULL | NULL | NULL | NULL | 1 | 2 |
12. +------+------+------+------+------+------+------+
13. 3 rows in set (0.00 sec)
15. 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;
16. +------+------+------+------+------+------+------+
17. | b | I | II | III | IV | V | VI |
18. +------+------+------+------+------+------+------+
19. | 1 | 1 | 2 | NULL | NULL | NULL | NULL |
20. | 2 | NULL | NULL | 1 | 2 | NULL | NULL |
21. | 3 | NULL | NULL | NULL | NULL | 1 | 2 |
22. +------+------+------+------+------+------+------+
23. 3 rows in set (0.00 sec)
25. 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;
26. +------+---------+----------+-----------+----------+---------+----------+
27. | b | I_TOTAL | II_TOTAL | III_TOTAL | IV_TOTAL | V_TOTAL | VI_TOTAL |
28. +------+---------+----------+-----------+----------+---------+----------+
29. | 1 | 1 | 2 | NULL | NULL | NULL | NULL |
30. | 2 | NULL | NULL | 1 | 2 | NULL | NULL |
31. | 3 | NULL | NULL | NULL | NULL | 1 | 2 |
32. +------+---------+----------+-----------+----------+---------+----------+
33. 3 rows in set (0.00 sec)
35. 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;
36. +------+---------+---+----------+----+-----------+-----+----------+----+---------+---+----------+----+
37. | b | I_TOTAL | I | II_TOTAL | II | III_TOTAL | III | IV_TOTAL | IV | V_TOTAL | V | VI_TOTAL | VI |
38. +------+---------+---+----------+----+-----------+-----+----------+----+---------+---+----------+----+
39. | 1 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 |
40. | 2 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 |
41. | 3 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 |
42. +------+---------+---+----------+----+-----------+-----+----------+----+---------+---+----------+----+
43. 3 rows in set (0.00 sec)
45. 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;
46. +------+---------+-------+----------+--------+-----------+---------+----------+--------+---------+-------+----------+--------+
47. | 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 |
48. +------+---------+-------+----------+--------+-----------+---------+----------+--------+---------+-------+----------+--------+
49. | 1 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 |
50. | 2 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 | NULL | 0 | NULL | 0 |
51. | 3 | NULL | 0 | NULL | 0 | NULL | 0 | NULL | 0 | 1 | 1 | 2 | 1 |
52. +------+---------+-------+----------+--------+-----------+---------+----------+--------+---------+-------+----------+--------+
53. 3 rows in set (0.00 sec)
55. greatsql> CREATE TABLE t2(a INT, b INT, c INT, d INT);
56. 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);
58. 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;
59. +------+------+------+------+------+------+------+
60. | b | 1_1 | 2_2 | 3_1 | 4_2 | 5_1 | 6_2 |
61. +------+------+------+------+------+------+------+
62. | 1 | 1 | 2 | NULL | NULL | NULL | NULL |
63. | 2 | NULL | NULL | 1 | 2 | NULL | NULL |
64. | 3 | NULL | NULL | NULL | NULL | 1 | 2 |
65. +------+------+------+------+------+------+------+
66. 3 rows in set (0.00 sec)
问题反馈
联系我们
扫码关注微信公众号
