개요
나는 MySQL 을 통해 각 [닉네임]이 보유한 [리뷰 수]와 [업로드 수]를 한 테이블에서 보고 싶었다.
그러나 내 능력으론 [닉네임]이 보유한 [리뷰 수]와 [닉네임]이 보유한 [피드 수]를 각각 따로 볼 수 밖에 없었다.
구글링을 통해 UNION 절을 확인했지만 응용이 어려워 아직 인류의 재앙이되기 전인 chatGPT 의 도움을 받아보았다.
요약
1)합치고 싶은 테이블 쿼리를 각각 짠다.
※두 테이블에는 기준값이 되는 하나의 프라이머리 키가 있어야 한다. (나의 경우 닉네임)
2)두 쿼리를 하나의 테이블에서 보여주기 위해서는 UNION ALL 구문을 활용한다
3)두 쿼리를 FROM 절에 넣어 한번 감싸고 SELECT로 최종적으로 필요한 컬럼을 작성한다.
※이때 각 쿼리의 컬럼 구성을 맞춰줘야 한다.
4)최종 쿼리
SELECT 닉네임, 리뷰 수, 피드 수
FROM (
A쿼리
UNION ALL
B쿼리
) AS TEMP
해결과정
1.내가 보고 싶은 테이블을 엑셀에 그려본다.
2.내 능력 상 하나의 쿼리로 두개를 합칠 수 없어 각 쿼리를 따로따로 작성해 본다.
1번 쿼리: 닉네임에 따른 리뷰 수를 출력한다. (작성 안한 리뷰는 제외)
select a3.nickname, count(a3.nickname) as review
from tb_tag_review a1
join tb_tag a2
on a1.id = a2.tag_review_id
join tb_member a3
on a2.member_id = a3.id
where a1.review != ' '
group by a3.nickname;
2번 쿼리: 닉네임에 따른 피드 수를 출력한다.
select a3.nickname, count(a3.nickname) as feed
from tb_feed a4
join tb_member a3
on a4.member_id = a3.id
group by a3.nickname
order by feed desc;
3.치트키 chatGPT 에게 합쳐달라 한다.
ok human,
SELECT nickname, SUM(review) AS total_review, SUM(feed) AS total_feed
FROM (
SELECT a3.nickname, COUNT(a3.nickname) AS review, 0 AS feed
FROM tb_tag_review a1
JOIN tb_tag a2 ON a1.id = a2.tag_review_id
JOIN tb_member a3 ON a2.member_id = a3.id
WHERE a1.review != ' '
GROUP BY a3.nickname
UNION ALL
SELECT a3.nickname, 0 AS review, COUNT(a3.nickname) AS feed
FROM tb_feed a4
JOIN tb_member a3 ON a4.member_id = a3.id
GROUP BY a3.nickname
) AS temp
GROUP BY nickname
ORDER BY total_feed DESC;
4.copy & paste
원하던 결과를 얻었다.
5. 배운점
1)UNION ALL: 두 가지 쿼리를 하나의 테이블에서 보여줄 수 있다.
2)AS temp : 두 개 테이블을 감싸는 가상의 테이블 생성 (a쿼리 union all b쿼리) as temp
"AS temp" 구문은 UNION ALL로 결합된 두 개의 쿼리 결과를 담을 가상 테이블을 생성하는 것입니다. 즉, UNION ALL로 결합된 결과를 "temp"라는 가상의 테이블에 저장하고, 이 가상 테이블을 기반으로 다시 SELECT 문을 실행합니다.
가상 테이블은 실제 데이터베이스 테이블은 아니지만, SELECT 문을 실행할 때는 마치 실제 테이블처럼 사용할 수 있습니다. 이렇게 가상 테이블을 생성하여 다시 SELECT 문을 실행하면, 복잡한 쿼리를 간단하게 구성할 수 있습니다.
위의 쿼리에서는 "AS temp" 구문을 사용하여 UNION ALL로 결합된 결과를 "temp"라는 가상 테이블에 저장합니다. 이 가상 테이블은 nickname, review, feed 3개의 컬럼을 가지고 있으며, 각각의 컬럼은 첫 번째 쿼리와 두 번째 쿼리에서 계산된 값들을 담고 있습니다. 이 가상 테이블을 기반으로 다시 SELECT 문을 실행하여 결과를 출력합니다.
3) 0 : A, B 테이블 구조를 통일시키기 위해 0의 값을 가지는 컬럼 추가 (이건 컬럼의 벨류가 숫자일 경우)
"0 AS FEED"는 "FEED"라는 이름의 컬럼을 생성하고, 그 컬럼의 값을 0으로 설정하는 것입니다. 이는 첫 번째 쿼리와 두 번째 쿼리의 컬럼 구조를 맞추기 위한 것입니다.
첫 번째 쿼리에서는 "review"라는 이름의 컬럼만 사용하고 있고, 두 번째 쿼리에서는 "feed"라는 이름의 컬럼만 사용하고 있기 때문에, 두 쿼리를 UNION ALL로 결합할 때 컬럼 구조를 맞춰주어야 합니다. 따라서 첫 번째 쿼리에는 "0 AS FEED"라는 구문을 추가하여 "FEED"라는 이름의 0 값을 가지는 컬럼을 생성해주었습니다.
결과적으로 UNION ALL로 결합된 결과 테이블에는 "nickname", "review", "feed" 3개의 컬럼이 존재하게 됩니다. 첫 번째 쿼리에서는 "feed" 컬럼이 0으로 채워지고, 두 번째 쿼리에서는 "review" 컬럼이 0으로 채워지게 됩니다.
※컬럼의 벨류가 개수(count)가 아닌 문자열이면 0이 아닌 해당 컬럼을 그대로 적어 준다.
6.복습
[닉네임/휴대폰번호/댓글 수] 를 한 테이블에서 보고싶다.
SELECT nic, phone, comment
FROM (
select a1.nickname as nic, a1.phone_number as phone, 0 as comment
from tb_member a1
union all
select a1.nickname as nic, a1.phone_number as phone, count(a2.content) as comment
from tb_comment a2
join tb_member a1
on a2.member_id = a1.id
group by a1.nickname
) as temp
WHERE phone is not NULL and comment != '0'
order by comment desc;