안녕하세요 박성원입니다.

오랜만에 사랑넷에 놀러왔네요

문의하신

조인 피벗의 경우 보통 매우 복잡도가 높아집니다. 물런, 내공이 높으신분은 후다닥 하시겠지만

저는 보통 python으로 계산해서 뿌리는것을 선호하죠 ^^;;

여튼 쿼리를 이용한 해결법은 더 손이 많이 가는데요 (컬럼이름을 일일이 수정해줘야합니다. ;;)

아래와 같이 풀어 낼 수 있습니다.

drop table worker;
drop table workingtime;

CREATE TABLE worker (
 workid varchar(255) not null default '',
 workname varchar(255) not null default ''
);
create table workingtime(
 workid varchar(255) not null default '',
 workinggubun varchar(255) not null default '',
 workingdate datetime not null default '0000-00-00',
 workingtime int not null default 0
);

insert into  worker values ('111111111','A');
insert into  worker values ('222222222','B');

insert into workingtime values ('111111111','null',now() - interval 1 Day,1);
insert into workingtime values ('111111111','null',now() - interval 2 Day,1);
insert into workingtime values ('222222222','null',now() - interval 1 Day,3);
insert into workingtime values ('222222222','null',now() - interval 2 Day,1);
insert into workingtime values ('222222222','null',now() - interval 3 Day,3);
insert into workingtime values ('222222222','null',now() - interval 4 Day,1);

select * from worker;
select * from workingtime;

SELECT now() + interval 1 Day

 
SELECT
 workid, workname,
 if(column_A is null, 0, column_A) as column_A,
 if(column_B is null, 0, column_A) as column_B,
 if(column_C is null, 0, column_A) as column_C
FROM
( SELECT T2.workid, T2.workname,
 sum(CASE T2.DayDiff WHEN 1 THEN T2.summery  END) as 'column_A',
 sum(CASE T2.DayDiff WHEN 2 THEN T2.summery  END) as 'column_B',
 sum(CASE T2.DayDiff WHEN 3 THEN T2.summery  END) as 'column_C'
from (
 SELECT workid, workname, DayDiff, sum(workingtime) as summery
  FROM (
   SELECT A.workid, A.workname, datediff(now(),workingdate) as DayDiff, workingtime
   FROM worker A inner join workingtime B on A.workid=B.workid ) T1
 group by workname, DayDiff ) T2
group by T2.workid, T2.workname) as T3