从垂直行中选择记录并转换水平行 [英] Select records from vertical row and transform horizontal row
问题描述
我正在处理 SQL Server 查询,以获取将处理多个任务代码的用户列表的最后一个 Badge_In_Out_Time.
I am working on a SQL Server query to fetch the last Badge_In_Out_Time of the list of users who will be working on multiple task codes.
task_mgmt - 表名
task_mgmt - tablename
表格列如下
Task_Sn | 用户名 | Task_Code | 动作 | Badge_IN_OUT_TIME |
---|---|---|---|---|
1 | 安迪 | 博客 | 开始 | 2021-07-15 08:11:45.000 |
2 | 安迪 | 博客 | END | 2021-07-15 10:11:45.000 |
3 | 贝基 | ACCTS | 开始 | 2021-07-15 11:11:45.000 |
4 | 克里斯 | 税 | 开始 | 2021-07-15 12:11:45.000 |
5 | 安迪 | 社交媒体 | 开始 | 2021-07-15 12:15:45.000 |
6 | 贝基 | ACCTS | END | 2021-07-15 12:25:45.000 |
结果除外
用户名 | Task_Code | Badge_IN_TIME | Badge_OUT_TIME |
---|---|---|---|
安迪 | 博客 | 2021-07-15 08:11:45.000 | 2021-07-15 10:11:45.000 |
贝基 | ACCTS | 2021-07-15 11:11:45.000 | 2021-07-15 12:25:45.000 |
克里斯 | 税 | 2021-07-15 12:11:45.000 | |
安迪 | 社交媒体 | 2021-07-15 12:15:45.000 |
我真的想不出对此有任何疑问.我是 SQL 的初学者,我知道 CRUD 操作.这对我来说似乎超级复杂.
I really couldn't come up with any query for this. I am a beginner in SQL and I know CRUD operations. This seems to be super complex for me.
Select UserName,
Task_Code,
Badge_In_Time,
Badge_Out_Time
from task_mgmt
order by Badge_IN_OUT_TIME desc
推荐答案
这是一种间隙和岛屿问题,岛屿是成对的START
和 END
.
有多种解决方案.在这里,我假设 START
总是定义每个组的开始,并且可能有也可能没有 END
.
There are a number of solutions. Here, I have assumed that START
always defines the beginning of each group, and there may or may not be an END
.
我们可以通过使用窗口化的COUNT
,按UserName
和Task_Code
进行分区来计算每个岛屿的分组数.然后我们也简单地按新的分组编号分组,并旋转时间戳.
We can calculate a grouping number for each island by using a windowed COUNT
, partitioning by UserName
and Task_Code
. We then simply group up also by the new grouping number, and pivot the timestamp.
SELECT
t.UserName,
t.Task_Code
MIN(CASE WHEN t.Action = 'START' THEN t.Badge_IN_OUT_TIME END) Badge_IN_TIME,
MAX(CASE WHEN t.Action = 'END' THEN t.Badge_IN_OUT_TIME END) Badge_OUT_TIME
FROM (
SELECT *,
COUNT(CASE WHEN t.Action = 'START' THEN 1 END) OVER
(PARTITION BY t.UserName, t.Task_Code
ORDER BY t.Badge_IN_OUT_TIME ROWS UNBOUNDED PRECEDING) grp
FROM task_mgmt t
) t
GROUP BY
t.UserName,
t.Task_Code
t.grp;
这篇关于从垂直行中选择记录并转换水平行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!