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