无丶丶丶丶丶丶丶聊
2019-06-27 14:36
SELECT T.CI_ID, WM_CONCAT(T.STU_NAME) FROM (SELECT S.STU_NAME, C.CI_ID, INSTR(C.STU_IDS, S.STU_ID) FROM PM_CI C, PM_STU S WHERE INSTR(C.STU_IDS, S.STU_ID) > 0) T GROUP BY T.CI_ID
SELECT S.STU_NAME, C.CI_ID, INSTR(C.STU_IDS, S.STU_ID)
FROM PM_CI C, PM_STU S
WHERE INSTR(C.STU_IDS, S.STU_ID) > 0
子查询结果
select ci_id,MAX(M) from (
select ci_id,wm_concat(stu_name) over (partition by c.ci_id order by s.stu_id)m from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)>0)e group by e.ci_id;
vm_concat()函数拼接得到的结果是乱序的,需要在给他排序,好像要借助
SELECT * FROM (SELECT A.*,ROW_NUMBER() OVER (PARTITION BY 根据去重字段 ORDER BY 去重时的排序字段) RN FROM 换成表名 A ) WHERE RN=1
Oracle高级查询
62883 学习 · 144 问题
相似问题