by le_penseur [SQL Query] [2019.11.21 17:13:03]
안녕하세요. 얼마전에 스칼라 쿼리를 조인으로 변경하는 법을 질문했었습니다. 감사하게도 마농님의 도움으로 해결했으나 몇가지 문제에 해답을 찾지 못하여 또 질문을 남깁니다...ㅠㅠ 1. 대상 테이블의 FK가 2개이고, 출력 레코드 수를 1개로 제한할 경우 아래 소스에서 컬럼 like_idx에 해당합니다. 2. SELECT 값이 COUNT(*)일 경우 (그룹 함수 사용) - 조인 후 해당 테이블의 COUNT를 얻어오고 싶습니다. 한마디로 count(테이블.*)로 표현할 수 있겠네요. 이를 대체할 수 있는 방법이 궁금합니다.. 아래 소스에서 컬럼 reg_user_reviews_count, like_count에 해당합니다. SQL에 대한 지식이 얕다보니 자세하게 설명을 하고 싶어도 부족함이 많습니다..ㅠㅠ 위의 질문에서 필요한 지식이나 용어도 추가적으로 설명해주시면 너무 감사하겠습니다. SELECT (select idx from user where user.idx = re.reg_user_idx) as user_idx, (select all_like.idx from all_like where all_like.type = 'review' and all_like.like_user_idx = user_idx and all_like.target_idx = re.idx limit 1 offset 0) as like_idx, (select count(*) from reviews where reviews.reg_user_idx = user_idx) as reg_user_reviews_count, (select count(*) from all_like where all_like.target_idx = re.idx and all_like.type = 'review') as like_count FROM reviews as re
1. 첫번째 서브쿼리는 불필요한 서브쿼리인 듯 한데요?
- 그냥 re.reg_user_idx 아닌가요?
2. 두번째 서브쿼리는 뭐가 문제인지 쿼리만 봐서는 모릅니다.
- 테이블의 PK 정보 라던가? 인덱스 정보를 알려주세요.
- 두 테이블간의 관계를 설명해 주세요.
- 샘플 자료를 보여주세요.
3. 세번째 서브쿼리의 조건절에 있는 user_idx 는 re.user_idx 이겠죠?
- 두번째 서브쿼리도 user_idx 가 있네요?
4, 쿼리만 달랑 올리지 마시고
- 각 테이블의 역할과 서로간의 관계에 대한 설명
- 각 서브쿼리의 역할을 설명해 주세요.
- 각 테이블의 PK 정보와 쿼리에 사용된 항목들의 의미
5. MySQL 버전도 알려주세요.
6. 서브쿼리만 올리셨는데?
- 메인 테이블 항목은 하나도 조회를 안하나요?
- 메인쿼리는 페이징 처리를 하지는 않나요?
쿼리가 길어서 복잡할까봐 줄였는데 그게 오히려 독이 됐군요...ㅠㅠ
전체 쿼리 올렸습니다 !
select re.content, re.c_state, re.reg_date, re.reg_user_idx, re.score, re.play_year, re.play_month, re.play_day, re.idx, re.c_user_profile_url, re.c_write_date, IFNULL(us.idx, 0) AS user_idx, IFNULL(us.nickname, '') AS user_name, fi.file_url as user_profile, ifnull(al1.idx, 0) as like_state, ifnull((select idx from all_like where type = 'review' and like_user_idx = user_idx and target_idx = re.idx limit 1 offset 0), 0) as like_idx, (select count(*) from reviews where reg_user_idx = user_idx) as reg_user_reviews_count, (select count(*) from all_like where target_idx = re.idx and type = 'review') as like_count from (SELECT reviews.* from reviews LEFT OUTER JOIN ( SELECT t1.rank, t1.idx FROM (select @rank:= @rank + 1 as rank , t.idx from (SELECT us.idx FROM user us INNER JOIN user_grade_list ug ON us.idx = ug.user_idx ) as t, (SELECT @rank := 0) as rank_reset ) AS t1 ) AS badge_rank ON badge_rank.idx = reviews.reg_user_idx where reviews.ground_idx = {$ground_idx} and reviews.review_type = 0 and reviews.content != '' order by {$order_column} desc, reviews.c_state asc, CASE WHEN reviews.c_state = 0 THEN reviews.{$orderColumn} WHEN reviews.c_state = 1 THEN reviews.c_write_date ELSE NULL END {$orderType} LIMIT {$limit} OFFSET {$offset} ) as re LEFT OUTER JOIN user us ON re.reg_user_idx = us.idx LEFT OUTER JOIN (SELECT file_url, reference_idx FROM files WHERE type = 'image' and reference_table = 'user' and identity = 'profile' GROUP by reference_idx ) AS fi ON re.reg_user_idx = fi.reference_idx LEFT OUTER JOIN (SELECT idx, target_idx FROM all_like WHERE type = 'review' and like_user_idx = {$user_idx} GROUP by target_idx ) AS al1 ON re.idx = al1.target_idx
문제가 많은 질문이었네요...번거로우셨을텐데 꼼꼼하게 되물어주셔서 정말 감사합니다 ㅠㅠ
질문에 대한 답변입니다.
1. 첫번째 서브쿼리는 조인한 유저의 좋아요 상태를 가져오는 코드입니다.
2. 등록 유저가 지금까지 등록한 리뷰 수를 가져오는 쿼리입니다. 서브쿼리가 아니고 JOIN으로 빼고 싶습니다.
3. 네 맞습니다. 서브 쿼리가 좋지 않다고하여 조인문으로 빼려고 하는데 count 값이라 어떻게 처리해야 할 지 모르겠습니다.
4. 리뷰의 좋아요 수와 등록 유저 정보를 보여주려고 합니다. 유저 정보와 리뷰에는 사진이 존재하며 files라는 테이블에 들어있습니다. [리뷰 - reg_user_idx, 좋아요 - like_user_idx, 파일 - reference_idx]는 us의 idx(PK)와 키로 맺어져있습니다. 파일의 경우에는 reference_table이라는 컬럼으로 리뷰의 사진인지, 유저의 사진인지 구분합니다.
리뷰(re), 좋아요(al1), 사진(fi), 등록 유저 정보(us)
5. MySQL 5.7.x입니다.
6. 59번째 줄에서 페이징 처리를 하고 있습니다.
1. 궁금했던 서브쿼리 안의 user_idx 는
- 메인쿼리의 IFNULL(us.idx, 0) AS user_idx 의 알리아스 부분을 가져오는 듯 하네요.
- 이것도 비표준 구문인 듯 하네요. MySQL 에서만 되는 코드인 듯 합니다.
- user_idx 라는 알리아스를 쓰지 말고 그냥 us.idx 나 re.reg_user_idx 로 적어주는게 명확합니다.
2. re 와 us 를 LEFT 조인하는데?
- INNER JOIN 해도 되는 것 아닌지요?
- 사용자가 이닌데도 리뷰를 등록할 수 있나요?
3. 스칼라서브쿼리를 조인으로 변환하는 것은
- 전체 조회 쿼리라면 그룹바이 후 조인하는 것이 유리할 수 있지만
- 페이지 처리 쿼리라면 서브쿼리가 더 유리 할 수 있습니다.
- 마찬가지 이유로 fi 의 파일을 가져오는 부분도 조인으로 전환하는 것이 유리한지 의문입니다.