根据列的值将多行分组为一行

vlurs2pr  于 2022-09-18  发布在  Java
关注(0)|答案(3)|浏览(166)

我想根据列的值将多行分组为一行。

给定此数据:

SELECT EmployeeID, RoomId , [Event], EventDate 
FROM TimeLog
WHERE EmployeeID = '107733' 
  AND EventDate BETWEEN '2020/02/26 00:00:00' AND  '2020/02/26 23:59:59' 
ORDER BY EventDate

EmployeeID|RoomID|Event|EventDate
-|-|
107733|05-27F|登录|2020-02-26 07:02:00
107733|05-27F|注销|2020-02-26 08:38:00
107733|05-25F|登录|2020-02-26 08:39:00
107733|05-25F|注销|2020-02-26 08:51:00
107733|05-27F|登录|2020-02-26 08:52:00
107733|05-27F|注销|2020-02-26 12:00:00

因此,基于Event列的值,该列包含两个可能的值-LoginLogout,我想将两行合并为一行,结果如下所示:
EmployeeID|RoomID|Login|注销
-|-|
107733|05-27F|2020-02-26 07:02:00|2020-02-26 08:38:00
107733|05-25F|2020-02-26 08:39:00|2020-02-26 08:51:00
107733|05-27F|2020-02-26 08:52:00|2020-02-26 12:00:00

其他要求(如果可能):

  • 应按时间顺序排序
  • (边框大小写)如果“Login”丢失或没有值,它应该得到Logout的值,如果“Logout”没有值,它应该得到“Login”字段的值。因此,如果缺少一个,登录和注销将具有相同的值。

EmployeeID|RoomID|Event|EventDate
-|-|
107733|05-27F|登录|2020-02-26 07:02:00
107733|05-25F|注销|2020-02-26 08:38:00

请注意,在本例中,这发生在不同的“Room”中。在05-27F上没有注销,而在05-25F上没有登录。预期结果为:

EmployeeID|RoomID|Login|注销
-|-|
107733|05-27F|2020-02-26 07:02:00|2020-02-26 07:02:00
107733|05-25F|2020-02-26 08:38:00|2020-02-26 08:38:00

我试图解决这个问题:

SELECT 
    EmployeeID, 
    RoomID, 
    'Login' = (SELECT TOP 1 EventDate 
               FROM TimeLog li 
               WHERE li.EmployeeID = tl.EmployeeID 
                 AND li.RoomID = tl.RoomID 
                 AND li.[Event] = 1),
    'Logout' = (SELECT TOP 1 EventDate 
                FROM TimeLog lo 
                WHERE lo.EmployeeID = tl.EmployeeID 
                  AND lo.RoomID = tl.RoomID 
                  AND lo.[Event] = 2)
FROM  
    TimeLog tl
WHERE 
    tl.EmployeeID = '107733'
    AND tl.EventDate BETWEEN '2020/02/26 00:00:00' AND '2020/02/26 23:59:59'
sirbozc5

sirbozc51#

我相信这应该能达到你想要的效果:DBFiddle Example

with cte as (
  select row_number() over (partition by EmployeeId, RoomId order by EventDate) r
  , *
  from TimeLog
)
select coalesce(i.EventDate, o.EventDate) LoginDate
, coalesce(o.EventDate, i.EventDate) LogoutDate
, coalesce(i.EmployeeId, o.EmployeeId) EmployeeId_
, coalesce(i.RoomId, o.RoomId)  RoomId_
from (select * from cte where Event = 'Login') i
full outer join (select * from cte where Event = 'Logout') o
on o.EmployeeId = i.EmployeeId
and o.RoomId = i.RoomId
and o.r = i.r+1
order by LoginDate, LogoutDate, EmployeeId_, RoomId_

这是通过对每个员工/房间组合的事件排序来实现的。

如果在登录之后立即出现注销(即,序列号(R)或注销事件比登录事件的序列号多1),则两者是相关的&放在同一行中。

如果您获得的任何行的登录没有连续注销,或者没有之前登录的注销,则这些行被视为具有丢失的数据,因此您在相应的注销时间/登录时间列中得到NULL。

希望这是有意义的&有帮助--如果你需要更深入的解释,请告诉我。

用于创建演示数据的代码

create table TimeLog (
  EmployeeId bigint not null,
  RoomId nchar(6) not null,
  Event nvarchar(6) not null,
  EventDate DateTime not null
)
insert TimeLog (EmployeeID, RoomId, Event, EventDate)
values (107733, '05-27F',   'Login',    '2020-02-26 07:02:00')
     , (107733, '05-27F',   'Logout',   '2020-02-26 08:38:00')
     , (107733, '05-25F',   'Login',    '2020-02-26 08:39:00')
     , (107733, '05-25F',   'Logout',   '2020-02-26 08:51:00')
     , (107733, '05-25F',   'Login',    '2020-02-27 08:39:00')
     , (107733, '05-25F',   'Logout',   '2020-02-27 08:51:00')
     , (107733, '05-25X',   'Login',    '2020-02-27 08:39:00')
     , (107733, '05-25Y',   'Logout',   '2020-02-27 08:51:00')

输出示例

LoginDate|LogoutDate|EmployeeId_|RoomID_
-|-|
107733|05-27F|2020-02-26 07:02:00.000|2020-02-26 08:38:00.000
2020-02-26 08:39:00.000|2020-02-26 08:51:00.000|2020|05-25F
107733|05-25X|2020-02-27 08:39:00.000|2020-02-27 08:39:00.000
2020-02-27 08:39:00.000|2020-02-27 08:51:00.000|2020|05-25F
107733-02-27 08:51:00.000|2020-02-27 08:51:00.000|2020-02-27 08:51:25

跟进问题答案

试试这个:DB Fiddle

with LoginCte as (
  select row_number() over (partition by EmployeeId, RoomId order by EventDate) LoginNo
  , EmployeeId
  , RoomId
  , EventDate LoginDate
  from TimeLog
  where Event = 'Login'
)
, LogoutCte as (
  select count(icte.LoginNo) PriorLoginCount
  , octe.EmployeeId
  , octe.RoomId
  , EventDate LogoutDate
  from TimeLog octe
  left outer join LoginCte icte 
  on icte.EmployeeId = octe.EmployeeId
  and icte.RoomId = octe.RoomId
  where Event = 'Logout'
  group by octe.EmployeeId, octe.RoomId, octe.Eventdate
)
select coalesce(i.EmployeeId, o.EmployeeId) EmployeeId_
, coalesce(i.RoomId, o.RoomId) RoomId_
, LoginDate
, LogoutDate
from LoginCte i
full outer join logoutCte o
on o.EmployeeId = i.EmployeeId
and o.RoomId = i.RoomId
and o.PriorLoginCount = i.LoginNo

它的工作原理是像以前一样为每个登录提供一个连续的ID;对于注销,它计算在它之前出现的登录次数。然后,可以将这些数字组合在一起,以确保最新的登录始终与其最早的相应注销配对;而不会丢失任何记录。

  • 首次登录之前发生的任何注销记录的priorLoginCount将为0
  • 第一条登录记录的loginNo1。在该登录之后但在下一次登录之前发生的所有注销记录的priorLoginCount1
  • 第二条登录记录的loginNo2。在该登录之后但在第三次登录之前发生的所有注销记录的priorLoginCount2
  • 如果我们在上次注销记录之后有任何登录记录,则它们的loginNo值将高于任何注销记录的priorLoginCount值。

因此,我们可以根据这些值将我们的记录配对。

nx7onnlm

nx7onnlm2#

如果您坚定地选择了SQL Server,那么Pivot也是一个选择:

SELECT
    EmployeeID,
    RoomID,
    ISNULL([Login], [Logout]) AS LatestLogin,
    ISNULL([Logout], [Login]) AS LatestLogout
FROM
    TimeLog
PIVOT (
    MAX(EventDate)
    FOR Event IN ([Login], [Logout])
    ) AS PivotTable
ORDER BY
    LatestLogin
;
w6lpcovy

w6lpcovy3#

也许是这样的:

SELECT EmployeeID, RoomId,
    MAX(CASE WHEN Event = 'Login'  THEN EventDate ELSE NULL END) AS LatestLogin,
    MAX(CASE WHEN Event = 'Logout' THEN EventDate ELSE NULL END) AS LatestLogout,
FROM Wherever
GROUP BY EmployeeID, RoomId

更新:每天第一次登录和最后一次注销。

SELECT w.EmployeeID, w.RoomId, d.Date,
    MIN(CASE WHEN Event = 'Login'  THEN EventDate ELSE NULL END) AS LatestLogin,
    MAX(CASE WHEN Event = 'Logout' THEN EventDate ELSE NULL END) AS LatestLogout
FROM Wherever w
    INNER JOIN (
        -- Find the dates. This is the PK of the result.
        DISTINCT EmployeeId, RoomId, CAST(EventDate AS date) AS Date
        FROM Wherever
    ) AS d
        ON w.EmployeeId = d.EmployeeId AND w.RoomId = d.RoomId AND CAST(w.EventDate AS date) = w.LatestDate
GROUP BY w.EmployeeID, w.RoomId, d.Date

相关问题