我有一個表是考勤的一個時間表
ID staff_no action date Time
1 123456 IN 2007-7-27 9:00
2 123456 OUT 2007-7-27 11:00
3 123456 IN 2007-7-27 12:00
4 123456 OUT 2007-7-27 6:00我想做到staff_no date IN1 OUT1 IN2 OUT2
123456 2007-7-27 9:00 11:00 12:00 6:00交叉表只能滿足有一個IN和OUT 不能把後面的顯示出來
ID staff_no action date Time
1 123456 IN 2007-7-27 9:00
2 123456 OUT 2007-7-27 11:00
3 123456 IN 2007-7-27 12:00
4 123456 OUT 2007-7-27 6:00我想做到staff_no date IN1 OUT1 IN2 OUT2
123456 2007-7-27 9:00 11:00 12:00 6:00交叉表只能滿足有一個IN和OUT 不能把後面的顯示出來
ID staff_no action date time
1 123456 in1 2007-7-27 9
2 123456 out1 2007-7-27 11
3 123456 in2 2007-7-27 12
4 123456 out2 2007-7-27 18
--执行查询
SELECT
staff_no,date,
sum(iif(action='in1',time,0)) as in1,
sum(iif(action='out1',time,0)) as out1,
sum(iif(action='in2',time,0)) as in2,
sum(iif(action='out1',time,0)) as out2
FROM 表1
group by staff_no,date;
--查询结果
staff_no date in1 out1 in2 out2
123456 2007-7-27 9 11 12 11