데이터베이스 질문과 답변
SELECT a.acode
, a.aname
,IF( MAX(CASE WHEN B.MENUID = 'menu1' THEN 'Y' ELSE 'N' END) ='Y', '제출', '미제출') AS menu1
,IF( MAX(CASE WHEN B.MENUID = 'menu2' THEN 'Y' ELSE 'N' END) ='Y', '제출', '미제출') AS menu3
,IF( MAX(CASE WHEN B.MENUID = 'menu3' THEN 'Y' ELSE 'N' END) ='Y', '제출', '미제출') AS menu3,
,IF( MAX(CASE WHEN B.MENUID = 'menu1' THEN 'Y'ELSE 'N' END) ='Y', b.wrdt, '') AS menu1Wrdt,
,IF( MAX(CASE WHEN B.MENUID = 'menu2' THEN 'Y'ELSE 'N' END) ='Y', b.wrdt, '') AS menu2Wrdt,
,IF( MAX(CASE WHEN B.MENUID = 'menu3' THEN 'Y'ELSE 'N' END) ='Y', b.wrdt, '') AS menu3Wrdt,
FROM code a
LEFT OUTER JOIN Rcheck b
ON a.acode = b.association
AND b.syear = '2018'
AND b.smonth = '07'
GROUP BY a.acode, a.aname
;
mysql 쿼리질문드리겠습니다.
메뉴별로 제출/미제출여부, 제출날짜를 구하는쿼리입니다.
제출/미제출은 제대로 결과를 가져오는데
날짜가 따로나와야하는데 하나로만 가져오는거같은데
어디를 수정해야하는지 답변부탁드립니다.
by 우리집아찌
[2018.07.16 14:11:13]
샘플 데이터 와 원하시는 데이터를 올려주세요.
by 마농
[2018.07.16 14:43:39]
SELECT a.acode
, a.aname
, IFNULL(MAX(CASE WHEN b.menuid = 'menu1' THEN '제출' END), '미제출') AS menu1
, IFNULL(MAX(CASE WHEN b.menuid = 'menu2' THEN '제출' END), '미제출') AS menu2
, IFNULL(MAX(CASE WHEN b.menuid = 'menu3' THEN '제출' END), '미제출') AS menu3
, MAX(CASE WHEN b.menuid = 'menu1' THEN b.wrdt END) AS menu1Wrdt
, MAX(CASE WHEN b.menuid = 'menu2' THEN b.wrdt END) AS menu2Wrdt
, MAX(CASE WHEN b.menuid = 'menu3' THEN b.wrdt END) AS menu3Wrdt
FROM code a
LEFT OUTER JOIN Rcheck b
ON a.acode = b.association
AND b.syear = '2018'
AND b.smonth = '07'
GROUP BY a.acode, a.aname
;
) 버튼을 클릭하여 작성 하시면 됩니다.