listagg

1.创建数据表，准备测试数据

CREATE TABLE MK_STUDENT(
    ID NUMBER(18) NOT NULL,
    STU_NAME VARCHAR2(200),
    STU_SCORE VARCHAR2(50),
    PRIMARY KEY (ID)
);
ALTER TABLE MK_STUDENT ADD KEMU VARCHAR2(200);

INSERT INTO MK_STUDENT (ID,STU_NAME,STU_SCORE,KEMU) VALUES (1,'小明',80,'英语');
INSERT INTO MK_STUDENT (ID,STU_NAME,STU_SCORE,KEMU) VALUES (2,'小明',60,'数学');
INSERT INTO MK_STUDENT (ID,STU_NAME,STU_SCORE,KEMU) VALUES (3,'小明',90,'语文');

INSERT INTO MK_STUDENT (ID,STU_NAME,STU_SCORE,KEMU) VALUES (4,'小红',66,'英语');
INSERT INTO MK_STUDENT (ID,STU_NAME,STU_SCORE,KEMU) VALUES (5,'小红',90,'数学');
INSERT INTO MK_STUDENT (ID,STU_NAME,STU_SCORE,KEMU) VALUES (6,'小红',90,'语文');

2.查出每个学生的所有优秀科目（大于等于80），每个学生使用一条数据展示
使用子查询来做
0.1先查出所有学生的优秀科目

SELECT a.stu_name,a.stu_score
FROM 
MK_STUDENT a
WHERE 
a.stu_score >= 80

SELECT base.stu_name,listagg(base.KEMU,',')
WITHIN GROUP (ORDER BY base.stu_name)
FROM
(SELECT a.stu_name,a.stu_score,a.kemu
FROM 
MK_STUDENT a
WHERE 
a.stu_score >= 80) base
GROUP BY
base.stu_name

SELECT b1.stu_name,b1.优秀科目,b2.劣势科目 FROM 
(SELECT base.stu_name,listagg(base.KEMU,',')
WITHIN GROUP (ORDER BY base.stu_name) AS 优秀科目
FROM
(SELECT a.stu_name,a.stu_score,a.kemu
FROM 
MK_STUDENT a
WHERE 
a.stu_score >= 80) base
GROUP BY
base.stu_name) b1 ,

(SELECT base.stu_name,listagg(base.KEMU,',')
WITHIN GROUP (ORDER BY base.stu_name)  AS 劣势科目
FROM
(SELECT a.stu_name,a.stu_score,a.kemu
FROM 
MK_STUDENT a
WHERE 
a.stu_score < 80) base
GROUP BY
base.stu_name) b2
WHERE 
b1.stu_name = b2.stu_name