수업에 3개 이상 등록한 학생을 출력하고싶은데요! 0 2 712

by 웹린이 [2022.04.05 17:17:28]


강좌정보테이블(TB_CLASS)와 각 강좌마다 등록된 회원테이블(TB_CLASS_USER)이 있습니다

강좌정보를 등록시 해당 강좌에 등록한 학생정보를 함께 입력하고

강좌정보(TB_CLASS)와 함께 강좌정보(SEQ)를 FK로 하여 학생정보(TB_CLASS_USER)들이 등록됩니다 

즉 한명의 학생이 여러 강좌정보에 들어가 있을 수 있습니다 (동일한지는 USER_CI 컬럼으로 체크할수 있습니다)

 

TB_CLASS에는 SEQ, TITLE이 있고

TB_CLASS_USER에는 CLASS_SEQ(FK), SEQ, USER_NAME, USER_AGE, USER_SEQ, USER_CI(사용자 식별값) 등 이 있습니다 

(위의 회색으로 된 컬럼명은 null값도 있습니다 )

 

이때 3개 이상 강좌에 참여하는 학생을 아래와 같이 출력하고싶습니다

학생명 참여하는 강좌명 개인식별값
김수지 수학,과학,영어 fajd09123adfkjlasdf123123
김수현 영어 dfajlk123sdfadfdafdf123541

아래와 같이 쿼리문을 짯는데 이게 맞을까요?

종류는 오라클입니다

    SELECT NVL(MAX(A.NAME),' ') NAME, WM_CONCAT(B.TITLE) AS CLASS_LIST, USER_CI
     FROM TB_CLASS_USER A, TB_CLASS B
     WHERE 1 = 1
     AND A.CLASS_SEQ = B.SEQ
    GROUP BY A.USER_CI
    HAVING COUNT(DISTINCT A.CLASS_SEQ) > 2

또한 만약 개인식별값(USER_CI)가 null인 사람이 여러명이면 이 또한 어떻게 처리 해야할까요? 

 

 

by 신이만든지기 [2022.04.05 18:01:42]

성능을 고려하지 않고 만들었습니다.

create table tb_class (
 seq number,
 title varchar2(10)
);

create table tb_class_user (
  seq number,
  user_name varchar2(10),
  user_ci varchar2(10),
  class_seq number
);

delete from tb_class;

insert into tb_class values (1, 'korean');
insert into tb_class values (2, 'english');
insert into tb_class values (3, 'mathematic');

select * from tb_class;

delete from tb_class_user;

insert into tb_class_user values (1, 'aa', '1111', 1);
insert into tb_class_user values (2, 'bb', null, 1);
insert into tb_class_user values (3, 'cc', '3333', 1);

insert into tb_class_user values (4, null, '1111', 2);
insert into tb_class_user values (5, 'bb', '2222', 2);
insert into tb_class_user values (6, 'cc', '3333', 2);

insert into tb_class_user values (7, 'dd', '1111', 3);
insert into tb_class_user values (8, 'cc',  null, 3);


select * from tb_class_user;

    select a.user_name, a.user_ci, b.title, count(1) over(partition by a.user_ci) cnt
     from tb_class_user a
         , tb_class b
  where a.class_seq = b.seq
  and a.user_ci is not null
  ;

-- 원하는 결과
select nvl(max(user_name), '') user_name,
      listagg(title, ',') within group(order by title) title,
      user_ci
 from (
    select a.user_name, a.user_ci, b.title, count(1) over(partition by a.user_ci) cnt
     from tb_class_user a
         , tb_class b
  where a.class_seq = b.seq
    and a.user_ci is not null
  )
  where cnt > 2
 group by user_ci;
 

 


by 마농 [2022.04.06 08:08:58]

user_ci 가 null 인 자료가 들어오는게 가능한 건가요?
- 업무 로직 검토 필요합니다.
- 가능하다면 왜 그런지 상세 설명이 필요합니다.
Group By 에 a.name 을 추가하면 MAX 필요 없습니다.
NVL 은 굳이 필요 없을 듯 하구요.
Distinct 가 필요한지도 검토 필요합니다.
wm_concat 는 비공식 함수입니다. 11G 라면 ListAgg 이용하세요.
 

SELECT a.user_ci
     , a.name
     , WM_CONCAT(b.title) class_list_10g
     , LISTAGG(b.title, ',') WITHIN GROUP(ORDER BY b.seq) class_list_11g
  FROM tb_class_user a
     , tb_class b
 WHERE a.class_seq = b.seq
 GROUP BY a.user_ci, a.name
--HAVING COUNT(DISTINCT a.class_seq) > 2
--HAVING COUNT(a.class_seq) > 2
HAVING COUNT(*) > 2
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입