데이터베이스 질문과 답변
with product_table as (
select 'pd1' as product union all
select 'pd2' as product union all
select 'pd3' as product union all
select 'pd4' as product
),
category_table as (
select 'cate1' as category union all
select 'cate2' as category union all
select 'cate3' as category union all
select 'cate4' as category
),
product_category_table as (
select 'pd1' as product, 'cate1' as category union all
select 'pd1' as product, 'cate2' as category union all
select 'pd1' as product, 'cate3' as category union all
select 'pd1' as product, 'cate4' as category union all
select 'pd2' as product, 'cate2' as category union all
select 'pd2' as product, 'cate3' as category union all
select 'pd2' as product, 'cate4' as category union all
select 'pd3' as product, 'cate1' as category union all
select 'pd3' as product, 'cate2' as category union all
select 'pd3' as product, 'cate3' as category
)
select product, count(product) as cnt
from product_category_table
where category in ('cate1','cate2','cate3')
group by product
HAVING cnt = 3;
product_table과 category_table 이 존재하고 둘을 category에 product를 진열하는 product_category 테이블이 있습니다.
product_category테이블 에서 'cate2,cate3,cate4'에 모두 진열된 상품을 조회하는 쿼리입니다.
category는 유동적이고 그에따라 cnt=3 부분을 필터링하려는 category개수로 변경해야 합니다.
현재 위처럼 구현 했는데 좀더 나은 방법이 있는지 도움 부탁드립니다.
by ㅇㅇ준
[2020.02.21 13:52:17]
WITH PRODUCT_TABLE AS (
SELECT 'PD1' AS PRODUCT FROM DUAL UNION ALL
SELECT 'PD2' AS PRODUCT FROM DUAL UNION ALL
SELECT 'PD3' AS PRODUCT FROM DUAL UNION ALL
SELECT 'PD4' AS PRODUCT FROM DUAL
),
CATEGORY_TABLE AS (
SELECT 'CATE1' AS CATEGORY FROM DUAL UNION ALL
SELECT 'CATE2' AS CATEGORY FROM DUAL UNION ALL
SELECT 'CATE3' AS CATEGORY FROM DUAL UNION ALL
SELECT 'CATE4' AS CATEGORY FROM DUAL
),
PRODUCT_CATEGORY_TABLE AS (
SELECT 'PD1' AS PRODUCT, 'CATE1' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD1' AS PRODUCT, 'CATE2' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD1' AS PRODUCT, 'CATE3' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD1' AS PRODUCT, 'CATE4' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD2' AS PRODUCT, 'CATE2' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD2' AS PRODUCT, 'CATE3' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD2' AS PRODUCT, 'CATE4' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD3' AS PRODUCT, 'CATE1' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD3' AS PRODUCT, 'CATE2' AS CATEGORY FROM DUAL UNION ALL
SELECT 'PD3' AS PRODUCT, 'CATE3' AS CATEGORY FROM DUAL
)
SELECT PRODUCT, COUNT(DISTINCT CATEGORY), COUNT(DISTINCT CATEGORY) AS CNT
FROM PRODUCT_CATEGORY_TABLE AA
WHERE CATEGORY IN ('CATE1','CATE2','CATE3')
GROUP BY PRODUCT
HAVING COUNT(DISTINCT CATEGORY) = (SELECT COUNT(*) FROM CATEGORY_TABLE AA WHERE AA.CATEGORY IN ('CATE1','CATE2','CATE3'));
by cellion
[2020.02.21 15:12:15]
답변 감사합니다.
좀 더 복잡해졌네요
by 마농
[2020.02.21 14:14:44]
네. 질문에 사용하신 쿼리에 문제가 없어 보입니다.
다만, 표준에 어긋난 쿼리네요. MySQL 에서만 오류 없이 동작하겠네요.
SQL 표준에 맞도록 작성하는게 좋을 듯합니다.
- 변경전 : HAVING cnt = 3
- 변경후 : HAVING COUNT(*) = 3
by cellion
[2020.02.21 15:11:53]
답변 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.