时间戳

首页 > 解决方案 > Laravel SQL Problem (I want to specify Last IN and Last OUT time from date)

sql - Laravel SQL Problem (I want to specify Last IN and Last OUT time from date)

问题描述

My SQL Table Data Is

UserID         Timestamp             EventTrigger
0000003   2019-08-27 08:57:23.000        IN
0000003   2019-08-27 09:07:23.000        OUT
0000003   2019-08-27 09:10:24.000        IN
0000003   2019-08-27 18:05:21.000        OUT

I want to make Output like 

UserID     Date         Last-IN         Last-OUT
 3      2019-08-27      09:10:24        18:05:21

标签： sqllaravel

解决方案

正如其他人所说，你需要更具体一点，以便人们能够给出好的答案，但如果你只想要一个 SQL 语句来提供我认为你所要求的输出，那么这应该可以解决问题：

select 
    UserId, 
    [IN] as [Last-IN], 
    [OUT] as [Last-OUT]
from
(
    select
        convert(int,UserId) as UserID,
        EventTrigger,
        max([Timestamp]) as LastDate
    from inout
    group by UserId, EventTrigger
) SourceTable
PIVOT
(
    max(LastDate)
    for EventTrigger in ([IN],[OUT])
) as PivotTable

这是基于您的基础表定义和数据如下：

create table inout
(
    UserId varchar(10),
    [Timestamp] datetime,
    EventTrigger varchar(3)
)

insert into inout values
('000003','2019-08-27 08:57:23.000','IN'),
('000003','2019-08-27 09:07:23.000','OUT'),
('000003','2019-08-27 09:10:24.000','IN'),
('000003','2019-08-27 18:05:21.000','OUT')

您可以使用子查询同样获得相同的结果：

select distinct
    convert(int,UserId) as UserID,
    (select max([Timestamp]) from inout lastin where a.UserId = lastin.UserId and lastin.EventTrigger = 'IN') as [Last-IN],
    (select max([Timestamp]) from inout lastout where a.UserId = lastout.UserId and lastout.EventTrigger = 'OUT') as [Last-OUT]
from inout a

或者可能通过一些其他方法，如自连接，或对每个 EventTrigger 值的单独查询......

推荐阅读