mysql竖表转横表的一种思路

select '本月' as months,sum(case when date=1 and Status=1 then 1 when date=1 and Status=2 then 2 when date=1 and Status=3 then 3 else 0 end) as day1,
sum(case when date=2 and Status=1 then 1 when date=2 and Status=2 then 2 when date=2 and Status=3 then 3 else 0 end) as day2,
sum(case when date=3 and Status=1 then 1 when date=3 and Status=2 then 2 when date=3 and Status=3 then 3 else 0 end) as day3,
sum(case when date=4 and Status=1 then 1 when date=4 and Status=2 then 2 when date=4 and Status=3 then 3 else 0 end) as day4,
sum(case when date=5 and Status=1 then 1 when date=5 and Status=2 then 2 when date=5 and Status=3 then 3 else 0 end) as day5,
sum(case when date=6 and Status=1 then 1 when date=6 and Status=2 then 2 when date=6 and Status=3 then 3 else 0 end) as day6,
sum(case when date=7 and Status=1 then 1 when date=7 and Status=2 then 2 when date=7 and Status=3 then 3 else 0 end) as day7,
sum(case when date=8 and Status=1 then 1 when date=8 and Status=2 then 2 when date=8 and Status=3 then 3 else 0 end) as day8,
sum(case when date=9 and Status=1 then 1 when date=9 and Status=2 then 2 when date=9 and Status=3 then 3 else 0 end) as day9,
sum(case when date=10 and Status=1 then 1 when date=10 and Status=2 then 2 when date=10 and Status=3 then 3 else 0 end) as day10,
sum(case when date=11 and Status=1 then 1 when date=11 and Status=2 then 2 when date=11 and Status=3 then 3 else 0 end) as day11,
sum(case when date=12 and Status=1 then 1 when date=12 and Status=2 then 2 when date=12 and Status=3 then 3 else 0 end) as day12,
sum(case when date=13 and Status=1 then 1 when date=13 and Status=2 then 2 when date=13 and Status=3 then 3 else 0 end) as day13,
sum(case when date=14 and Status=1 then 1 when date=14 and Status=2 then 2 when date=14 and Status=3 then 3 else 0 end) as day14,
sum(case when date=15 and Status=1 then 1 when date=15 and Status=2 then 2 when date=15 and Status=3 then 3 else 0 end) as day15,
sum(case when date=16 and Status=1 then 1 when date=16 and Status=2 then 2 when date=16 and Status=3 then 3 else 0 end) as day16,
sum(case when date=17 and Status=1 then 1 when date=17 and Status=2 then 2 when date=17 and Status=3 then 3 else 0 end) as day17,
sum(case when date=18 and Status=1 then 1 when date=18 and Status=2 then 2 when date=18 and Status=3 then 3 else 0 end) as day18,
sum(case when date=19 and Status=1 then 1 when date=19 and Status=2 then 2 when date=19 and Status=3 then 3 else 0 end) as day19,
sum(case when date=20 and Status=1 then 1 when date=20 and Status=2 then 2 when date=20 and Status=3 then 3 else 0 end) as day20,
sum(case when date=21 and Status=1 then 1 when date=21 and Status=2 then 2 when date=21 and Status=3 then 3 else 0 end) as day21,
sum(case when date=22 and Status=1 then 1 when date=22 and Status=2 then 2 when date=22 and Status=3 then 3 else 0 end) as day22,
sum(case when date=23 and Status=1 then 1 when date=23 and Status=2 then 2 when date=23 and Status=3 then 3 else 0 end) as day23,
sum(case when date=24 and Status=1 then 1 when date=24 and Status=2 then 2 when date=24 and Status=3 then 3 else 0 end) as day24,
sum(case when date=25 and Status=1 then 1 when date=25 and Status=2 then 2 when date=25 and Status=3 then 3 else 0 end) as day25,
sum(case when date=26 and Status=1 then 1 when date=26 and Status=2 then 2 when date=26 and Status=3 then 3 else 0 end) as day26,
sum(case when date=27 and Status=1 then 1 when date=27 and Status=2 then 2 when date=27 and Status=3 then 3 else 0 end) as day27,
sum(case when date=28 and Status=1 then 1 when date=28 and Status=2 then 2 when date=28 and Status=3 then 3 else 0 end) as day28,
sum(case when date=29 and Status=1 then 1 when date=29 and Status=2 then 2 when date=29 and Status=3 then 3 else 0 end) as day29,
sum(case when date=30 and Status=1 then 1 when date=30 and Status=2 then 2 when date=30 and Status=3 then 3 else 0 end) as day30,
sum(case when date=31 and Status=1 then 1 when date=31 and Status=2 then 2 when date=31 and Status=3 then 3 else 0 end) as day31,
sum(case when Status=1 then 1 else 0 end) as attendce,
sum(case when Status=2 then 1 else 0 end) as absence,
sum(case when Status=3 then 1 else 0 end) as waitwork
from(
select date,Status from (  -- 出勤日期数据及标记状态
select round(date_format(s.recorddate,'%d')) as date,s.Status
    from Personnel l
        inner join PersonnelSignIn s on l.Id=s.PersonnelId
            where l.Id='08d735e4-ded0-e2c0-4200-9b5170000001' and date_format(recorddate,'%y%m')=date_format(now(),'%y%m')
          ORDER BY date_format(s.recorddate,'%d')
)a)b limit 1
)c