데이터베이스 질문과 답변
select t1.empnum,
mgyear = '2020',
case max(date01)when 0 then '0' else CONVERT(nvarchar(3),max(date01)) end as date01,
case max(date02)when 0 then '0' else CONVERT(nvarchar(3),max(date02)) end as date02,
case max(date03)when 0 then '0' else CONVERT(nvarchar(3),max(date03)) end as date03,
case max(date04)when 0 then '0' else CONVERT(nvarchar(3),max(date04)) end as date04,
case max(date05)when 0 then '0' else CONVERT(nvarchar(3),max(date05)) end as date05,
case max(date06)when 0 then '0' else CONVERT(nvarchar(3),max(date06)) end as date06,
case max(date07)when 0 then '0' else CONVERT(nvarchar(3),max(date07)) end as date07,
case max(date08)when 0 then '0' else CONVERT(nvarchar(3),max(date08)) end as date08,
case max(date09)when 0 then '0' else CONVERT(nvarchar(3),max(date09)) end as date09,
case max(date10)when 0 then '0' else CONVERT(nvarchar(3),max(date10)) end as date10,
case max(date11)when 0 then '0' else CONVERT(nvarchar(3),max(date11)) end as date11,
case max(date12)when 0 then '0' else CONVERT(nvarchar(3),max(date12)) end as date12,
useday = (select sum (annual) from ANNUAL_COPY where smonth like '2020%'and empnum = t1.empnum)
from ( select
anc.empnum
,date01 = (select anc.annual where anc.smonth = concat('2020','01'))
,date02 = (select anc.annual where anc.smonth = concat('2020','02'))
,date03 = (select anc.annual where anc.smonth = concat('2020','03'))
,date04 = (select anc.annual where anc.smonth = concat('2020','04'))
,date05 = (select anc.annual where anc.smonth = concat('2020','05'))
,date06 = (select anc.annual where anc.smonth = concat('2020','06'))
,date07 = (select anc.annual where anc.smonth = concat('2020','07'))
,date08 = (select anc.annual where anc.smonth = concat('2020','08'))
,date09 = (select anc.annual where anc.smonth = concat('2020','09'))
,date10 = (select anc.annual where anc.smonth = concat('2020','10'))
,date11 = (select anc.annual where anc.smonth = concat('2020','11'))
,date12 = (select anc.annual where anc.smonth = concat('2020','12'))
from ANNUAL_COPY anc where SUBSTRING(anc.smonth,1,4) = '2020') t1 ,
ANNUAL_COPY anc where t1.empnum = anc.empnum
group by t1.empnum, anc.smonth, anc.annual
order by empnum
쿼리문은 이렇게 했고 ANNUAL_COPY의 테이블은
empnum, smonth, annual
아이디, 년월 , 횟수
라고 생각하시면 되겠습니다.
쿼리 결과 값이 월마다 나와서 이 결과를 년으로만 뜨게 하고 싶습니다.
SELECT empnum
, mgyear
, ISNULL(SUM(CASE mm WHEN '01' THEN annual END), 0) m01
, ISNULL(SUM(CASE mm WHEN '02' THEN annual END), 0) m02
, ISNULL(SUM(CASE mm WHEN '03' THEN annual END), 0) m03
, ISNULL(SUM(CASE mm WHEN '04' THEN annual END), 0) m04
, ISNULL(SUM(CASE mm WHEN '05' THEN annual END), 0) m05
, ISNULL(SUM(CASE mm WHEN '06' THEN annual END), 0) m06
, ISNULL(SUM(CASE mm WHEN '07' THEN annual END), 0) m07
, ISNULL(SUM(CASE mm WHEN '08' THEN annual END), 0) m08
, ISNULL(SUM(CASE mm WHEN '09' THEN annual END), 0) m09
, ISNULL(SUM(CASE mm WHEN '10' THEN annual END), 0) m10
, ISNULL(SUM(CASE mm WHEN '11' THEN annual END), 0) m11
, ISNULL(SUM(CASE mm WHEN '12' THEN annual END), 0) m12
, SUM(annual) useday
FROM (SELECT empnum
, annual
, SUBSTRING(smonth, 1, 4) mgyear
, SUBSTRING(smonth, 5, 2) mm
FROM annual_copy
WHERE smonth LIKE '2020%'
) a
GROUP BY empnum, mgyear
;
감사합니다! 바로 되네요 ㄷㄷ...
) 버튼을 클릭하여 작성 하시면 됩니다.