-- Start
假设有下面的表,每个人都有可能有多个 Email。
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID NUMBER(10) NOT NULL,
NAME VARCHAR2(20) NOT NULL
);
CREATE TABLE EMAIL
(
EMPLOYEE_ID NUMBER(10) NOT NULL,
EMAIL VARCHAR2(20) NOT NULL
);
INSERT INTO EMPLOYEE VALUES (1, '张三');
INSERT INTO EMAIL VALUES (1, 'san.zhang@163.com');
INSERT INTO EMAIL VALUES (1, 'san.zhang@qq.com');
INSERT INTO EMPLOYEE VALUES (2, '李四');
INSERT INTO EMAIL VALUES (2, 'li.si@163.com');
INSERT INTO EMAIL VALUES (2, 'li.si@qq.com');
现在让你写一个 SQL 返回下面的结果集,怎么办?
EMPLOYEE_ID NAME EMAIL
1 张三 san.zhang@163.com;san.zhang@qq.com
2 李四 li.si@163.com;li.si@qq.com
不会了吧,其实很简单,这是因为 Oracle 提供了一个叫做 LISTAGG 的函数,如下。
SELECT
E.EMPLOYEE_ID,
E.NAME,
LISTAGG(A.EMAIL, ';') WITHIN GROUP (ORDER BY A.EMAIL) EMAIL
FROM
EMPLOYEE E,
EMAIL A
WHERE
E.EMPLOYEE_ID = A.EMPLOYEE_ID
GROUP BY
E.EMPLOYEE_ID,
E.NAME;
--更多参见:Oracle SQL 精萃
-- 声明:转载请注明出处
-- Last edited on 2015-02-25
-- Created by ShangBo on 2015-02-25
-- End