안녕하세요 쿼리를 좀 단순화 하고 싶은데 가르침 부탁드립니다.
SELECT
*
FROM(
SELECT
A.bp_seq
, SUM(A.a18) AS sum18
, SUM(A.a11) AS sum11
, SUM(A.qty) AS sumqty
FROM(
SELECT
bp_seq
, IF(num1 <= 18 AND 18 <= num2, qty, 0) AS a18
, IF(num1 <= 11 AND 11 <= num2, qty, 0) AS a11
, qty
FROM
(
SELECT
p1.bp_seq
, p2.num1
, p2.num2
, p1.qty
FROM
p1
INNER JOIN
p2
ON
p2.seq = p1.p2_seq
) AS q
) AS A
GROUP BY A.bp_seq
) AS sum_table
WHERE
sum_table.sum18 >= 3 AND sum_table.sum11 >= 1
SELECT p1.bp_seq , IFNULL(SUM(CASE WHEN 18 BWTWEEN p2.num1 AND p2.num2 THEN p1.qty END), 0) sum18 , IFNULL(SUM(CASE WHEN 11 BWTWEEN p2.num1 AND p2.num2 THEN p1.qty END), 0) sum11 FROM p1 INNER JOIN p2 ON p2.seq = p1.p2_seq GROUP BY p1.bp_seq HAVING IFNULL(SUM(CASE WHEN 18 BWTWEEN p2.num1 AND p2.num2 THEN p1.qty END), 0) >= 3 AND IFNULL(SUM(CASE WHEN 11 BWTWEEN p2.num1 AND p2.num2 THEN p1.qty END), 0) >= 1 ;