개발 Q&A

제목 mysql case when then 에서 여러개의 결과를 뽑으려면 ?
글쓴이 람이 작성시각 2015/05/15 14:42:50
댓글 : 1 추천 : 0 스크랩 : 0 조회수 : 14708   RSS 
select a.prc_exchange, a.prc_halin, a.prc_price11, a.prc_price21, a.prc_price31, b.*,
(select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC from wiz_prdprice where prc_price31>0 AND it_id='".$_GET['prdcode']."' and prc_sdate between '".$start_month."' and '".$last_month."' order by LOWPRIC asc LIMIT 1) as price,
CASE a.prc_exchange
    WHEN 'KOR'
    THEN CAST((select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC from wiz_prdprice where prc_price31>0 AND it_id='".$_GET['prdcode']."' and prc_sdate between '".$start_month."' and '".$last_month."' order by LOWPRIC asc LIMIT 1) AS UNSIGNED)
    WHEN 'JPN'
    THEN CAST(((select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC from wiz_prdprice where prc_price31>0 AND it_id='".$_GET['prdcode']."' and prc_sdate between '".$start_month."' and '".$last_month."' order by LOWPRIC asc LIMIT 1) * (select ex_JPY1 from exchange order by ex_regdate DESC LIMIT 1)) AS UNSIGNED)
    WHEN 'USD'
    THEN CAST(((select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC from wiz_prdprice where prc_price31>0 AND it_id='".$_GET['prdcode']."' and prc_sdate between '".$start_month."' and '".$last_month."' order by LOWPRIC asc LIMIT 1) * (select ex_USD1 from exchange order by ex_regdate DESC LIMIT 1)) AS UNSIGNED)
    WHEN 'CHN'
    THEN CAST(((select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC from wiz_prdprice where prc_price31>0 AND it_id='".$_GET['prdcode']."' and prc_sdate between '".$start_month."' and '".$last_month."' order by LOWPRIC asc LIMIT 1) * (select ex_CNY1 from exchange order by ex_regdate DESC LIMIT 1)) AS UNSIGNED)
    WHEN 'ERP'
    THEN CAST(((select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC from wiz_prdprice where prc_price31>0 AND it_id='".$_GET['prdcode']."' and prc_sdate between '".$start_month."' and '".$last_month."' order by LOWPRIC asc LIMIT 1) * (select ex_EUR1 from exchange order by ex_regdate DESC LIMIT 1)) AS UNSIGNED)
    END SUM3
from wiz_prdprice as a, exchange as b
WHERE 1=1
AND b.ex_regdate = '".$row['ex_DATE']."'
AND a.it_id='".$_GET['prdcode']."'
AND a.prc_price31 > 0
GROUP BY a.it_id


위에 쿼리문에서 보시듯 현재는 prc_price31에 대하여 SUM3만 뽑고 있는데요 ?

이걸 각각 price1에 대하여는 SUM1, price2는 SUM2, price3은 SUM3으로 뽑을 수 있을까요 ?
 다음글 메일 관련!! (5)
 이전글 mysql query 문에 대한 질문요 ? (6)
목록

댓글

람이 / 2015/05/15 17:01:34 / 추천 0

$sql = "select *, substring(prc_sdate,9,2) as day_idx, prc_sdate as sdate,
(select CAST(prc_price11 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price11>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) as price1, 
(select CAST(prc_price21 AS UNSIGNED) AS LOWPRIC2 from wiz_prdprice where prc_price21>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) as price2, 
(select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC3 from wiz_prdprice where prc_price31>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) as price3, 
( select 
    CASE prc_exchange 
    WHEN 'KOR' THEN CAST((select CAST(prc_price11 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price11>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) AS UNSIGNED) 
    WHEN 'JPN' THEN CAST(((
        (select CAST(prc_price11 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price11>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_JPY1 from exchange order by ex_regdate DESC LIMIT 1)/100)    ) * 100) AS UNSIGNED) 
    WHEN 'USD' THEN CAST(((
        (select CAST(prc_price11 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price11>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_USD1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED) 
    WHEN 'CHN' THEN CAST(((
        (select CAST(prc_price11 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price11>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_CNY1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED)     
    WHEN 'ERP' THEN CAST(((
        (select CAST(prc_price11 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price11>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_EUR1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED)
    END SUM1
    from wiz_prdprice WHERE 1=1 and it_id='".$_GET['prdcode']."' group by it_id order by SUM1 asc) as pr1, 

( select 
    CASE prc_exchange 
    WHEN 'KOR' THEN CAST((select CAST(prc_price21 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price21>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) AS UNSIGNED) 
    WHEN 'JPN' THEN CAST(((
        (select CAST(prc_price21 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price21>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_JPY1 from exchange order by ex_regdate DESC LIMIT 1)/100)    ) * 100) AS UNSIGNED) 
    WHEN 'USD' THEN CAST(((
        (select CAST(prc_price21 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price21>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_USD1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED) 
    WHEN 'CHN' THEN CAST(((
        (select CAST(prc_price21 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price21>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_CNY1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED)     
    WHEN 'ERP' THEN CAST(((
        (select CAST(prc_price21 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price21>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_EUR1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED)
    END SUM2
    from wiz_prdprice WHERE 1=1 and it_id='".$_GET['prdcode']."' group by it_id order by SUM2 asc) as pr2, 

( select 
    CASE prc_exchange 
    WHEN 'KOR' THEN CAST((select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price31>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) AS UNSIGNED) 
    WHEN 'JPN' THEN CAST(((
        (select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price31>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_JPY1 from exchange order by ex_regdate DESC LIMIT 1)/100)    ) * 100) AS UNSIGNED) 
    WHEN 'USD' THEN CAST(((
        (select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price31>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_USD1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED) 
    WHEN 'CHN' THEN CAST(((
        (select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price31>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_CNY1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED)     
    WHEN 'ERP' THEN CAST(((
        (select CAST(prc_price31 AS UNSIGNED) AS LOWPRIC1 from wiz_prdprice where prc_price31>0 and it_id='".$_GET['prdcode']."' and prc_sdate=sdate) * round((select ex_EUR1 from exchange order by ex_regdate DESC LIMIT 1)/100)) * 100) AS UNSIGNED)
    END SUM3
    from wiz_prdprice WHERE 1=1 and it_id='".$_GET['prdcode']."' group by it_id order by SUM3 asc 
) as pr3
FROM wiz_prdprice 
WHERE 1=1 AND prc_price31 > 0 AND prc_price21 > 0 AND prc_price31 > 0 AND it_id='".$_GET['prdcode']."' AND prc_sdate between '".$start_month."' and '".$last_month."'
GROUP BY prc_sdate 
ORDER BY prc_sdate ASC
";


이렇게 해서 뽑긴 뽑았는데...

이게 맞을런지 모르겠네요 ㅠ.ㅜ