select 결과 로우에 따라 유동적인 쿼리 작성이 가능할까요? 0 9 1,382

by mjee [2018.07.03 17:37:24]


1. 구분 테이블 t_dept

dept_idx dept_join dept_name
1 0 야외 스포츠
2 1 축구
3 1 야구
4 2 아시안게임 축구 경기
5 2 월드컵 경기
6 3 KBO
7 3 MLB
8 0 실내스포츠
9 8 탁구
10 8 배드민턴
11 9 중국 탁구리그
12 9 한국 탁구리그
13 9 일본 탁구리그

 

2. user 테이블 t_user

user_idx user_name dept_idx
1 이승엽 (KBO) 6
2 이병규 (KBO) 6
3 선동렬 (KBO) 6
4 Scott Alexander (MLB) 7
5 이용대 (배드민턴) 10
6 이동수 (배드민턴) 10

 

3. 장비 테이블 t_equipment

equipment_idx equipment_name user_idx
1 엘지트윈스 배트 2
2 선동렬 글러브 3
3 갤럭시 S9 5
4 고급 배드민턴 공 6

 

안녕하세요!

위 세 테이블로 아래와 같은 조건의 데이터 select가 가능할까요?

1. 구분 테이블은 각 로우에 종속이 됩니다. dept_idx 컬럼에 상위 구분의 dept_idx가 들어가며, 최상위 구분일 경우 0이 들어갑니다.

2. 구분테이블의 하위 구분 등록은 무한대로 가능합니다.

 

원하는 출력 내용 : dept_idx를 지정 시 그 구분을 포함한 모든 하위 구분의 유저 수, 장비 수

ex) where dept_idx = 6

 result : 유저 : 3 , 장비 : 2

 

하위구분이 무한으로 가능해서 유동적(루프문 사용 등)으로 쿼리 작성이 필요할 것 같은데 이를 프로시저나 프로그램코딩 말고  

쿼리로만 작성이 가능할까요?

 

서울은 아침까지 흐리더니 해가떠서 덥네요..

모두 장마철 건강 조심하시기 바랍니다.

 

 

 

 

by 아발란체 [2018.07.03 18:14:50]

장비수 1개 아닌가영?

dept_idx = 6인 사람 3명에 그 3명이 가지고 있는 user_id는 1, 2, 3이고 장비에서 user_id 1하고 3은 없고 2만 있습니다 = 1개 


by 아발란체 [2018.07.03 18:15:11]

장비수 1개가 맞다면

WITH T_DEPT AS (
    SELECT 1 AS dept_idx, 0 AS dept_join, '야외 스포츠' AS dept_name FROM DUAL
    UNION ALL SELECT 2, 1, '축구' FROM DUAL
    UNION ALL SELECT 3, 1, '야구' FROM DUAL
    UNION ALL SELECT 4, 2, '아시안게임 축구 경기' FROM DUAL
    UNION ALL SELECT 5, 2, '월드컵 경기' FROM DUAL
    UNION ALL SELECT 6, 3, 'KBO' FROM DUAL
    UNION ALL SELECT 7, 3, 'MLB' FROM DUAL
    UNION ALL SELECT 8, 0, '실내스포츠' FROM DUAL
    UNION ALL SELECT 9, 8, '탁구' FROM DUAL
    UNION ALL SELECT 10, 8, '배드민턴' FROM DUAL
    UNION ALL SELECT 11, 9, '중국 탁구리그' FROM DUAL
    UNION ALL SELECT 12, 9, '한국 탁구리그' FROM DUAL
    UNION ALL SELECT 13, 9, '일본 탁구리그' FROM DUAL
), T_USER AS (
    SELECT 1 AS user_idx, '이승엽 (KBO)' AS user_name, 6 AS dept_idx FROM DUAL
    UNION ALL SELECT 2, '이병규 (KBO)', 6 FROM DUAL
    UNION ALL SELECT 3, '선동렬 (KBO)', 6 FROM DUAL
    UNION ALL SELECT 4, 'Scott Alexander (MLB)', 7 FROM DUAL
    UNION ALL SELECT 5, '이용대 (배드민턴)', 10 FROM DUAL
    UNION ALL SELECT 6, '이동수 (배드민턴)', 10 FROM DUAL
), T_EQUIPMENT AS (
    SELECT 1 AS equipment_idx, '엘지트윈스 배트' AS equipment_name, 2 AS user_idx FROM DUAL
    UNION ALL SELECT 2, '선동렬 글러브', 6 FROM DUAL
    UNION ALL SELECT 3, '갤럭시 S9', 10 FROM DUAL
    UNION ALL SELECT 4, '고급 배드민턴 공', 10 FROM DUAL
)
SELECT
    COUNT(*) AS 유저수, MAX((SELECT COUNT(*) FROM T_EQUIPMENT WHERE user_idx = T_USER.user_idx)) AS 장비수
FROM
    T_USER
WHERE
    dept_idx = 6
;

 


by 마농 [2018.07.03 18:19:21]

장비테이블의 키가 어떻게 되나요?
 - 장비ID 만 키인가요?  ==> 모든 장비는 한사람만 관리
 - 장비ID + 유저ID 가 키인가요? ==> 같은 장비를 여러명이 쓸 수 있음.

WITH t_dept AS
(
SELECT 1 dept_idx, 0 dept_join, '야외 스포츠' dept_name FROM dual
UNION ALL SELECT  2, 1, '축구'                 FROM dual
UNION ALL SELECT  3, 1, '야구'                 FROM dual
UNION ALL SELECT  4, 2, '아시안게임 축구 경기' FROM dual
UNION ALL SELECT  5, 2, '월드컵 경기'          FROM dual
UNION ALL SELECT  6, 3, 'KBO'                  FROM dual
UNION ALL SELECT  7, 3, 'MLB'                  FROM dual
UNION ALL SELECT  8, 0, '실내스포츠'           FROM dual
UNION ALL SELECT  9, 8, '탁구'                 FROM dual
UNION ALL SELECT 10, 8, '배드민턴'             FROM dual
UNION ALL SELECT 11, 9, '중국 탁구리그'        FROM dual
UNION ALL SELECT 12, 9, '한국 탁구리그'        FROM dual
UNION ALL SELECT 13, 9, '일본 탁구리그'        FROM dual
)
, t_user AS
(
SELECT 1 user_idx, '이승엽 (KBO)'  user_name, 6 dept_idx FROM dual
UNION ALL SELECT 2, '이병규 (KBO)'         ,  6 FROM dual
UNION ALL SELECT 3, '선동렬 (KBO)'         ,  6 FROM dual
UNION ALL SELECT 4, 'Scott Alexander (MLB)',  7 FROM dual
UNION ALL SELECT 5, '이용대 (배드민턴)'    , 10 FROM dual
UNION ALL SELECT 6, '이동수 (배드민턴)'    , 10 FROM dual
)
, t_equipment AS
(
SELECT 1 equipment_idx, '엘지트윈스 배트' equipment_name, 2 user_idx FROM dual
UNION ALL SELECT 2, '선동렬 글러브'   ,  6 FROM dual
UNION ALL SELECT 3, '갤럭시 S9'       , 10 FROM dual
UNION ALL SELECT 4, '고급 배드민턴 공', 10 FROM dual
)
SELECT COUNT(DISTINCT u.user_idx) user_cnt
     , COUNT(e.equipment_idx) equipment_cnt
--     , COUNT(DISTINCT e.equipment_idx) equipment_cnt
  FROM (SELECT *
          FROM t_dept
         START WITH dept_idx = 6
         CONNECT BY PRIOR dept_idx = dept_join
        ) d
     , t_user u
     , t_equipment e
 WHERE d.dept_idx = u.dept_idx(+)
   AND u.user_idx = e.user_idx(+)
;

 


by mjee [2018.07.03 21:14:20]

답장주신분들 감사합니다. 답장이 늦어죄송합니다. 장비테이블 두번째 로우 오타입니다유저idx 3으로 정정합니다. 그리고 말씀해주신대로 쿼리를 작성하면 데이터가 추가될때마다 쿼리를 수정해야되는것 아닌가요?? Deptidx만으로 조회하는건 쿼리로 불가능할까요?


by 마농 [2018.07.03 22:41:47]

데이터를 일일히 적어서 Union 하라는 의미가 아닙니다.
With 문은 테스트용 샘플데이터를 만든 것일 뿐입니다.
With 문이 아닌 쿼리문(Select 구문)을 보세요.


by mjee [2018.07.04 16:42:46]

넵 마농님 이해했습니다.

다만 제가 사용하는 DB를 적지않았었네요.. MYSQL사용중이라 START WITH CONNECT BY PRIOR를 사용하지못합니다.

대안을 생각해서 적용시켜보겠습니다.

답변주신 아발란체님 마농님 감사드립니다.


by 마농 [2018.07.04 16:54:29]

mySQL 버전을 알려주세요.
8.0 부터는 RECURSIVE WITH 구문으로 계층쿼리 구현이 가능합니다.


by mjee [2018.07.04 17:48:34]

5.7버전입니다!


by mjee [2018.07.05 14:26:49]
마농님 답변주신 내용 토대로 아래와 같이 고쳐서 완성했습니다. 도움주셔서 감사합니다!


SELECT 
       COUNT(DISTINCT u.user_idx) user_cnt
     , COUNT(i.equipment_idx) equipment_cnt
	 
  FROM (
		select  DEPT_IDX,
                            DEPT_NAME,
                           DEPT_JOIN
from    (select * from T_DEPT
          order by DEPT_JOIN, DEPT_IDX) products_sorted,
          (select @pv := '6') initialisation
where   (find_in_set(DEPT_JOIN, @pv)
          and  length(@pv := concat(@pv, ',', DEPT_IDX))) or dept_idx = 6		
		) d 
left join t_user u
on(d.dept_idx = u.DEPT_IDX)
left join T_EQUIPMENT i
on(u.user_idx = i.USER_IDX)

 

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