經(jīng)測試是正確的，可以參考
--查詢出每個科目對應(yīng)著那些學(xué)生
select c.ci_id cid,s.stu_name sname,instr(c.stu_ids,s.stu_id)?
from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)<>0
--將上面的查詢結(jié)果作為表進行分組查詢，顯示為想要的結(jié)果
select b1.cid,wm_concat(b1.sname) from?
(select c.ci_id cid,s.stu_name sname,instr(c.stu_ids,s.stu_id)?
from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)<>0) b1 group by b1.cid;
2020-03-24
1、先創(chuàng)建表及初始化數(shù)據(jù)
?①創(chuàng)建及初始化學(xué)生表PM_STU
create table PM_STU(STU_ID? ? ? ? ? ?VARCHAR2(20) NOT NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? STU_NAME? ? ? VARCHAR2(20));? ? ? ? ? ? ? ??
INSERT INTO? PM_STU VALUES(1,'張三');
INSERT INTO? PM_STU VALUES(2,'李四');
INSERT INTO? PM_STU VALUES(3,'王五');
INSERT INTO? PM_STU VALUES(4,'趙六');?
②創(chuàng)建及初始化選課表PM_CI
? 先創(chuàng)建一個PM_CI1來轉(zhuǎn)換成PM_CI
CREATE TABLE PM_CI1(CI_ID? ? ? ? VARCHAR2(20) NOT NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? STU_IDS?? VARCHAR2(20));? ? ? ? ? ? ? ? ?
INSERT INTO PM_CI1 VALUES(1,1);
INSERT INTO PM_CI1 VALUES(1,2);
INSERT INTO PM_CI1 VALUES(1,3);
INSERT INTO PM_CI1 VALUES(1,4);
INSERT INTO PM_CI1 VALUES(2,1);
INSERT INTO PM_CI1 VALUES(2,4);
創(chuàng)建PM_CI，并插入數(shù)據(jù)
CREATE TABLE PM_CI(CI_ID? ? ? ?VARCHAR2(20) NOT NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?STU_IDS?? VARCHAR2(20));
?
INSERT INTO? PM_CI? SELECT CI_ID,WM_CONCAT(STU_IDS)? FROM PM_CI1 GROUP BY CI_ID;
---先做出兩張表的笛卡爾積，再根據(jù)有效的條件instr(a.STU_IDS,b.STU_ID) !=0，過濾得到每個科目對應(yīng)的學(xué)生
? ? ? ?select? a.CI_ID ,b.STU_NAME
??????? from? PM_CI a,PM_STU b
??????? where? ?instr(a.STU_IDS,b.STU_ID) !=0;
? ---使用WM_CONCAT（）函數(shù)，列轉(zhuǎn)行
select? ?CI_ID,? ?WM_CONCAT(STU_NAME) SUT_NAME
from (
??????? select a.CI_ID ,b.STU_NAME
??????? from PM_CI a,PM_STU b
??????? where instr(a.STU_IDS,b.STU_ID) !=0
???????? )
group by CI_ID;
2020-03-24
示例二
找到員工表中薪水大于本部門平均薪水的員工
方法三：分析函數(shù)
select *
from (
????? select empno,ename,sal,
? ? ? ? ? ? ? ?avg(sal) over(partition by deptno) avg_sal
??????? from emp)?
where sal > avg_sal
order by avg_sal;