c# - 如何获取 MySQl Pivot 选择

Question

我在 MySql 中有一个查询

select User_ID,`date`,
if(weekday(`date`)=0,'Monday',
if(weekday(`date`)=1,'Tuesday',
if(weekday(`date`)=2,'Wenesday',
if(weekday(`date`)=3,'Thursday',
if(weekday(`date`)=4,'Friday',
if(weekday(`date`)=5,'Saturday',
if(weekday(`date`)=6,'Sunday',false))))))) as `Day`,
`In`,`Out`
from attendance 
where `date` between '2019-11-04' and '2019-11-10'
order by User_ID

结果:

如何得到这个结果？

Answer 1

使用条件聚合:

select 
    User_ID,
    `date`,
    min(case when weekday(`date`) = 0 then `In` end) monday_in,
    max(case when weekday(`date`) = 0 then `Out` end) monday_out,
    min(case when weekday(`date`) = 1 then `In` end) tuesday_in,
    max(case when weekday(`date`) = 1 then `Out` end) tuesday_out,
    min(case when weekday(`date`) = 2 then `In` end) wednesday_in,
    max(case when weekday(`date`) = 2 then `Out` end) wednesday_out,
    min(case when weekday(`date`) = 3 then `In` end) thursday_in,
    max(case when weekday(`date`) = 3 then `Out` end) thursday_out,
    min(case when weekday(`date`) = 4 then `In` end) friday_in,
    max(case when weekday(`date`) = 4 then `Out` end) friday_out,
    min(case when weekday(`date`) = 5 then `In` end) saturday_in,
    max(case when weekday(`date`) = 5 then `Out` end) saturday_out,
    min(case when weekday(`date`) = 6 then `In` end) sunday_in,
    max(case when weekday(`date`) = 6 then `Out` end) sunday_out
from attendance
where `date` between '2019-11-04' and '2019-11-10'
group by User_ID, `date`
order by User_ID, `date`

注意:in、out 和 date 为 reserved words in MySQL ;将它们用作列名并不是一个好主意(您必须使用反引号来引用它们，这在上述查询中会变得乏味)。