sql - SQL 查询将 values 转换为 value 中更改时的新列

我正在寻求有关员工打卡/退房查询的帮助。

我的代码目前是:

SELECT CAST(EVENTTIME AS Date) AS Date, FORMAT(EVENTTIME, 'HH:mm') AS Time,SUM(UserID) AS UserID, FirstName + Space(1) + Surname, EventSubTypeDescription
FROM EventsEx
WHERE EventTime >= '2022-05-09' AND (PeripheralName ='clock (In)' OR PeripheralName ='clock (Out)')
GROUP BY userid, EventTime, FirstName, Surname, EventSubTypeDescription
ORDER BY Date,UserID, Time ASC

结果是:

Date Time UserID UserName EventSubTypeDescription
2022-05-09 07:53 393 Jennifer Clock in
2022-05-09 13:33 393 Jennifer Clock out
2022-05-09 14:06 393 Jennifer Clock in
2022-05-09 16:57 393 Jennifer Clock out
2022-05-09 07:59 401 agency 2 Clock in
2022-05-09 12:58 401 agency 2 Clock out
2022-05-09 13:27 401 agency 2 Clock in
2022-05-09 16:56 401 agency 2 Clock out
2022-05-09 07:57 422 Tash Clock in
2022-05-09 13:56 422 Tash Clock out
2022-05-09 07:58 432 agency 4 Clock in
2022-05-09 13:00 432 agency 4 Clock out
2022-05-09 13:30 432 agency 4 Clock in
2022-05-09 16:56 432 agency 4 Clock out
2022-05-09 07:57 434 Jordan Clock in
2022-05-09 13:32 434 Jordan Clock out
2022-05-09 14:03 434 Jordan Clock in
2022-05-09 16:59 434 Jordan Clock out
2022-05-09 07:59 438 Adam Clock in
2022-05-09 12:59 438 Adam Clock out
2022-05-09 13:29 438 Adam Clock in
2022-05-09 16:56 438 Adam Clock out

每个用户在白天上班和下班。我需要将 Times 移动到单独的列,因此每个用户每天都有一行。

Date UserID Username EventSubTypeDescription Clock in Clock Out Clock in 2 Clock out 2
09/05/2022 393 Jennifer Clock in 07:53 13:33 14:06 16:57
09/05/2022 401 agency 2 Clock in 07:59 12:58 13:27 16:56
09/05/2022 422 Tash Clock in 07:57 13:56
09/05/2022 432 agency 4 Clock in 07:58 13:00 13:30 16:56
09/05/2022 434 Jordan Clock in 07:57 13:32 14:03 16:59
09/05/2022 438 Adam Clock in 07:59 12:59 13:29 16:56

回答1

从当前输出 table 开始,您可以通过对特定 date 和用户进行分区,为每一行分配一个列号。然后在 CASE 构造上使用 MAX 函数分别提取 clock inclock outclock in 2clock out 2 的 values:

SELECT Date,
       UserID,
       Username,
       MAX(CASE WHEN ColNum=1 THEN Time ELSE NULL END) AS Clock_in,
       MAX(CASE WHEN ColNum=2 THEN Time ELSE NULL END) AS Clock_out,
       MAX(CASE WHEN ColNum=3 THEN Time ELSE NULL END) AS Clock_in_2,
       MAX(CASE WHEN ColNum=4 THEN Time ELSE NULL END) AS Clock_out_2
FROM (SELECT *,
             ROW_NUMBER() OVER(PARTITION BY Date, UserID 
                               ORDER     BY Time         ) ColNum
      FROM output_table) ranked_clock_events
GROUP BY Date,
         UserID,
         Username
ORDER BY UserID

试试https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=df4a03745201401b09a2ad840238d9e4

旁注1:要将此查询与您的查询一起使用,只需将您的代码替换为 output table

旁注 2:为了更好地查询以避免中间结果(由 output_table 结果集给出),请在此处 ping 我以防您设法获得 EventEx table。

相似文章

python - Python - 在多行和多列中比较 values

我是Python的新手,我有一个如下所示的数据框(请参阅原始数据table)。最终目标是协调来自2个系统(sys1与sys2,在“源”列中标记)的记录(即id、rg、prd和数量)。我如何使用1个函数...

最新文章