데이터베이스 개발자 질문과 답변 게시판
SELECT
(case when a.SEX_CD= '1' AND a.LAST_GRADE='1+' AND b.WORK_FLG='0' THEN b.CARCASS_PRICE END) amt1 ,
(case when a.SEX_CD= '1' AND a.LAST_GRADE='1' AND b.WORK_FLG='1' THEN b.CARCASS_PRICE END) amt2 ,
(CASE when a.SEX_CD= '1' AND a.LAST_GRADE='2' AND b.WORK_FLG='2'THEN b.CARCASS_PRICE END) amt3
FROM M_GRADING_DTL A
LEFT join M_PIG_DAILY_PRICE b
ON b.WORK_DT=A.BUTCHERY_DT AND b.WORK_FLG='0'
LEFT join M_PIG_DAILY_PRICE b1
ON b1.WORK_DT=A.BUTCHERY_DT AND b1.WORK_FLG='1'
LEFT join M_PIG_DAILY_PRICE b2
ON b2.WORK_DT=A.BUTCHERY_DT AND b2.WORK_FLG='2'
WHERE b.WORK_DT='20150205'
AND A.BP_CD='02-0042'
위 CASE중 조건에 맞는 한가지만 값이 불러져야 합니다.
요렇게 해서 돌리면 case 의 맨 첫줄만 반영되고 나머지 두개는 반영이 안됩니다.
짱구 빡씨게 돌리고 있습니다.
Comment 2
-
건우아빠
2016.02.16 14:52
-
큘러
2016.02.16 15:19
SELECT
(case when a.SEX_CD= '1' AND a.LAST_GRADE='1+' AND b.WORK_FLG='0' THEN b.CARCASS_PRICE *a.DEAD_WEIGHT
when a.SEX_CD= '1' AND a.LAST_GRADE='1' AND b.WORK_FLG='1' THEN b.CARCASS_PRICE * a.DEAD_WEIGHT
when a.SEX_CD= '1' AND a.LAST_GRADE='2' AND b.WORK_FLG='2'THEN b.CARCASS_PRICE * a.DEAD_WEIGHT END) AMT,
a.SEX_CD, a.LAST_GRADE, b.WORK_FLG
FROM M_GRADING_DTL A
INNER JOIN M_IV_HDR D ON D.IV_NO = A.IV_NO
LEFT join M_PIG_DAILY_PRICE b ON b.WORK_DT=D.IV_DT
WHERE D.IV_DT='20150206'
AND A.BP_CD='02-0042'로 나열하면
444309.00000000 87.0000 1 1+ 0
NULL 87.0000 1 1+ 1
NULL 87.0000 1 1+ 2
NULL 87.0000 1 1+ 5
NULL 87.0000 1 1+ 4
NULL 87.0000 1 1+ 6
NULL 87.0000 1 1+ 7
NULL 87.0000 1 1+ 8
NULL 87.0000 1 1+ 9
NULL 85.0000 3 1 0
NULL 85.0000 3 1 1
449416.00000000 88.0000 1 1+ 0 이렇게 첫번째 조건만 따릅니다.
a.SEX_CD, a.LAST_GRADE, b.WORK_FLG 을 나열해 보세요...