원하는 쿼리가 나오지 않아서요. 도움 부탁드립니다.

Question

SELECT p.product_id,p.title, p.thumbnail_content, p.price, p.stock, p.status, c.category_id, c.code_name, min(f.store_id), f.store_file_name, f.upload_file_name FROM product p LEFT JOIN category c ON p.category_id &#61; c.category_id LEFT JOIN file_store f ON p.product_id &#61; f.id and f.board &#61; 'product' GROUP BY p.product_id;제가 의도하는 바는 group by로 product 리스트를 뽑는건데요. 등록한 product의 이미지중 첫번째 이미지가 group by할때 나오도록 하고 싶은데 잘 안되네요.. 도움부탁드립니다.미리 새해복 많이받으세요.

kenu,전독시 · Answer

LEFT 를 빼 보세요.

테스트맨 · Answer

SELECT p.product_id,p.title, p.thumbnail_content, p.price, p.stock, p.status, c.category_id, c.code_name, f.store_id, f.store_file_name, f.upload_file_name FROM product p LEFT JOIN category c ON p.category_id &#61; c.category_id LEFT JOIN file_store f ON p.product_id &#61; f.id AND f.store_id &#61; ( SELECT MAX(store_id) FROM file_store WHERE board &#61; 'product' AND id &#61; p.product_id ) GROUP BY p.product_id; 상상으로 짜봄 될지 안될지 모름

kenu,전독시 · Answer

image from: https://hongong.hanbit.co.kr/sql-기본-문법-joininner-outer-cross-self-join/

르매 · Answer

옵션에서 GROUP BY에 대해 엄격한 문법을 적용하지 않도록 하는 옵션이 켜져 있어서 오류가 나지 않는 것 같은데, 사실 본문의 쿼리는 문법적으로 옳지 않습니다.GROUP BY 절에 있는 컬럼 (product_id) 을 제외한 나머지 컬럼은 SELECT 절에서 항상 집계 함수 (MIN, MAX, SUM, COUNT, AVG.. 등)로 감싸야 하기 때문인데, 본문의 쿼리는 이 규칙을 지키고 있지 않기 때문입니다.이 문제를 푸는 방법은 두 가지가 있습니다.(1) file_store 테이블에서 board 값이 'product'인 행에 대해 id로 그룹핑한 결과를 서브 쿼리로 만들고, 그 이후에 테이블 조인에 사용하는 방법입니다.SELECT id AS product_id, MIN(store_id) AS store_id FROM file_store WHERE board &#61; 'product' GROUP BY id위 쿼리처럼 file_store 테이블에서만 그룹핑해서 상품 1개 당 가장 첫번째 이미지 1개를 먼저 가져오는 서브 쿼리를 만듭니다. 그런 후에 이 서브쿼리를 나머지 테이블과 조인해서 결과를 가져와야 맞습니다.가독성을 위해 FROM절에 사용하는 서브쿼리 대신 CTE를 사용하면, 최종 쿼리는 아래와 같게 됩니다.WITH s AS ( SELECT id AS product_id, MIN(store_id) AS store_id FROM file_store WHERE board &#61; 'product' GROUP BY id ) SELECT p.product_id, p.title, p.thumbnail_content, p.price, p.stock, p.status , c.category_id, c.code_name , f.store_id, f.store_file_name, f.upload_file_name FROM product p LEFT OUTER JOIN category c ON p.category_id &#61; c.category_id LEFT OUTER JOIN s ON p.product_id &#61; s.product_id LEFT OUTER JOIN file_store f ON s.store_id &#61; f.store_id ORDER BY p.product_id;(2) 하지만 위의 방법은 성능적으로 좋지 않습니다. file_store 같은 테이블의 성격 상 레코드가 많아서 GROUP BY를 실행하려면 CPU와 I/O를 많이 사용합니다. (좀 더 최적화할 수 있지만 너무 길어지니 생략합니다.)또한, 그룹핑한 결과 (&#61;서브쿼리)와 나머지 테이블을 조인할 때 사용할 수 있는 인덱스가 없다는 문제도 있습니다.따라서 첫 번째 방법은 그룹핑한 결과셋이 충분히 작을 때만 사용해야합니다.좀 더 성능까지 고려한 방법으로는 아래와 같이 Window Function을 사용하는 것입니다.SELECT p.product_id, p.title, p.thumbnail_content, p.price, p.stock, p.status , c.category_id, c.code_name , f.store_id, f.store_file_name, f.upload_file_name , ROW_NUMBER() OVER(PARTITION BY p.product_id ORDER BY f.store_id) AS row_num FROM product p LEFT OUTER JOIN category c ON p.category_id &#61; c.category_id LEFT OUTER JOIN file_store f ON p.product_id &#61; f.id AND f.board &#61; 'product'위의 쿼리에서 마지막 컬럼인 row_num은, 이 레코드에 있는 이미지가 해당 상품의 몇 번째 이미지인지를 알려 줍니다. 즉, 이 값이 1인 레코드는 첫 번째 이미지를 가진 상품이 되는거죠.하지만 ROW_NUMBER() OVER()와 같은 Window Function은 WHERE절에서 직접 사용할 수 없기 때문에 아래와 같이 서브쿼리 또는 CTE로 감싼 후.. 바깥에서 필터링해야 합니다.성능까지 고려한 최종 결과는 아래와 같습니다.WITH s AS ( SELECT p.product_id, p.title, p.thumbnail_content, p.price, p.stock, p.status , c.category_id, c.code_name , f.store_id, f.store_file_name, f.upload_file_name , ROW_NUMBER() OVER(PARTITION BY p.product_id ORDER BY f.store_id) AS row_num FROM product p LEFT OUTER JOIN category c ON p.category_id &#61; c.category_id LEFT OUTER JOIN file_store f ON p.product_id &#61; f.id AND f.board &#61; 'product' ) SELECT product_id, title, thumbnail_content, price, stock, status , category_id, code_name , store_id, store_file_name, upload_file_name FROM s WHERE row_num &#61; 1 ORDER BY product_id;

원하는 쿼리가 나오지 않아서요. 도움 부탁드립니다.

4개의 답변

Q&A 목록