華閱文章網1. SQL 統計每日上班打卡和下班打卡語句
select *_no,*_time as "上班時間"，*_time as "上班時間1",*_time as "上班時間2",*_time as "上班時間3",*_time as "下班時間"，*_time as "下班時間1",*_time as "下班時間2",*_time as "下班時間3" from (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID from attetime where doorinout = 1) T where *_ID = 1) A1 left join (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID from attetime where doorinout = 1) T where *_ID = 2) A2 on *_no = *_no left join (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID from attetime where doorinout = 1) T where *_ID = 3) A3 on *_no = *_no left join (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID from attetime where doorinout = 1) T where *_ID = 4) A4 on *_no = *_no full join (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID from attetime where doorinout = 0) T where *_ID = 1) B1 on *_no = *_no left join (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID from attetime where doorinout = 0) T where *_ID = 2) B2 on *_no = *_no left join (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID from attetime where doorinout = 0) T where *_ID = 3) B3 on *_no = *_no left join (select card_no, atte_time from (select card_no,atte_time,ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID from attetime where doorinout = 0) T where *_ID = 4) B4 on *_no = *_no 投入驗證數據如下：執行結果如下：。
