select D.userName as 用户姓名, C.用户ID as 用户ID, C.白班次数 as 白班次数, C.夜班次数 as 夜班次数, C.统计年月 as 统计年月 from
表1 as D,
(select A.用户ID as 用户ID, A.白班次数 as 白班次数, B.夜班次数 as 夜班次数, B.年月 as 统计年月 from
(select userId as 用户ID, isNull(count(dutyId),0) as 白班次数, to_char(dutyDate,'yyyyMM') as 年月 from 表2 where dutyType='1' group by userId,to_char(dutyDate,'yyyyMM') asc to_char(dutyDate,'yyyyMM')) as A
full out join
(select userId as 用户ID, isNull(count(dutyId),0) as 夜班次数, to_char(dutyDate,'yyyyMM') as 年月 from 表2 where dutyType='2' group by userId,to_char(dutyDate,'yyyyMM') asc to_char(dutyDate,'yyyyMM')) as B
on A.用户ID=B.用户ID, A.年月 = B.年月) as C)
where C.用户ID=D.userId
表1 as D,
(select A.用户ID as 用户ID, A.白班次数 as 白班次数, B.夜班次数 as 夜班次数, B.年月 as 统计年月 from
(select userId as 用户ID, isNull(count(dutyId),0) as 白班次数, to_char(dutyDate,'yyyyMM') as 年月 from 表2 where dutyType='1' group by userId,to_char(dutyDate,'yyyyMM') asc to_char(dutyDate,'yyyyMM')) as A
full out join
(select userId as 用户ID, isNull(count(dutyId),0) as 夜班次数, to_char(dutyDate,'yyyyMM') as 年月 from 表2 where dutyType='2' group by userId,to_char(dutyDate,'yyyyMM') asc to_char(dutyDate,'yyyyMM')) as B
on A.用户ID=B.用户ID, A.年月 = B.年月) as C)
where C.用户ID=D.userId