Oracle和mysql分组查询,Oracle和MySQL分組查詢GROUP BY"/>
oracle和mysql分组查询,Oracle和MySQL分組查詢GROUP BY
真題2、Oracle和MySQL中的分組(GROUP BY)后的聚合函數分別是什么?
答案:在Oracle中,可以用WM_CONCAT函數或LISTAGG分析函數;在MySQL中可以使用GROUP_CONCAT函數。示例如下:
首先給出建表語句:
CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR2(30),AUTHOR VARCHAR2(30),PRICE NUMBER); --Oracle
--CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR(30),AUTHOR VARCHAR(30),PRICE FLOAT); --MySQL oracle通用
INSERT INTO T_MAX_LHR VALUES ('0001','B',3.99);
INSERT INTO T_MAX_LHR VALUES ('0002','A',10.99);
INSERT INTO T_MAX_LHR VALUES ('0003','C',1.69);
INSERT INTO T_MAX_LHR VALUES ('0004','B',19.95);
INSERT INTO T_MAX_LHR VALUES ('0005','A',6.96);
COMMIT;
SELECT * FROM T_MAX_LHR;
在MySQL中:
mysql> SELECT T.AUTHOR, GROUP_CONCAT(T.ARTICLE), GROUP_CONCAT(T.PRICE)
-> FROM T_MAX_LHR T
-> GROUP BY T.AUTHOR;
+--------+-------------------------+-----------------------+
| AUTHOR | GROUP_CONCAT(T.ARTICLE) | GROUP_CONCAT(T.PRICE) |
+--------+-------------------------+-----------------------+
| A | 0002,0005 | 10.99,6.96 |
| B | 0001,0004 | 3.99,19.95 |
| C | 0003 | 1.69 |
+--------+-------------------------+-----------------------+
3 rows in set (0.00 sec)
在Oracle中:
LHR@orclasm > SELECT T.AUTHOR, WM_CONCAT(T.ARTICLE) ARTICLE, WM_CONCAT(T.PRICE) PRICE
2 FROM T_MAX_LHR T
3 GROUP BY T.AUTHOR;
AUTHOR ARTICLE PRICE
-------- --------------- ---------------
A 0002,0005 10.99,6.96
B 0001,0004 3.99,19.95
C 0003 1.69
LHR@orclasm > SELECT T.AUTHOR,
2 LISTAGG(T.ARTICLE, ',') WITHIN GROUP(ORDER BY T.PRICE) ARTICLE,
3 LISTAGG(T.PRICE, ',') WITHIN GROUP(ORDER BY T.PRICE) PRICE
4 FROM T_MAX_LHR T
5 GROUP BY T.AUTHOR;
AUTHOR ARTICLE PRICE
-------- --------------- ---------------
A 0005,0002 6.96,10.99
B 0001,0004 3.99,19.95
C 0003 1.69
原作者不知道是誰了,這個圖不是小麥苗畫的。
更多推荐
oracle和mysql分组查询,Oracle和MySQL分組查詢GROUP BY
发布评论