sql考勤查询

6yjfywim 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(349)

我想做一个类似于上一个问题的查询：考勤时间的sql查询
我的数据格式如下：

--------------------------------
    ID_Emp| Name    |   Date
    --------------------------------
    11    |Jon     |14/05/2014 8:00
    11    |Jon     |14/05/2014 12:00
    11    |Jon     |14/05/2014 12:30
    11    |Jon     |14/05/2014 16:00
    12    |Beth    |14/05/2014 8:00
    12    |Beth    |14/05/2014 12:00
    12    |Beth    |14/05/2014 12:30
    12    |Beth    |14/05/2014 16:00
    13    |Bob     |14/05/2014 8:00
    13    |Bob     |14/05/2014 12:00
    13    |Bob     |14/05/2014 12:30
    13    |Bob     |14/05/2014 16:00

但希望使用它以以下格式生成查询：

-----------------------------

    ID_Emp|Name    |Date            |TimeIn1 |TimeOut1 |TimeIn2 |TimeOut2
    ----------------------------------------------------------------------
    11    |Jon     |14/05/2014      |8:00    |12:00    |12:30   |16:00
    12    |Beth    |14/05/2014      |8:00    |12:00    |12:30   |16:00
    13    |Bob     |14/05/2014      |8:00    |12:00    |12:30   |16:00

sql sql-server-2008-r2

来源：https://stackoverflow.com/questions/62060680/sql-time-and-attendance-query

1条答案

按热度按时间

kokeuurv1#

你可以用 row_number() 和条件聚合：

select id_emp, name, convert(date, date),
       convert(time, max(case when seqnum = 1 then date end)) as timein1,
       convert(time, max(case when seqnum = 2 then date end)) as timein2,
       convert(time, max(case when seqnum = 3 then date end)) as timein3,
       convert(time, max(case when seqnum = 4 then date end)) as timein4
from (select t.*,
             row_number() over (partition by id_emp, convert(date, date) order by date) as seqnum
      from t
     ) t
group by id_emp, name, convert(date, date);

赞(0）回复(0）举报 2021-08-13

我来回答

sql考勤查询

1条答案

相关问题

热门标签

最新问答