时间戳

首页 > 解决方案 > 获取第一个开始时间和最后一个结束时间

sql - 获取第一个开始时间和最后一个结束时间

问题描述

我在第一张图片中有一个初始查询结果。

select 
    rpt_mem.Member_Full_Name as Member,
    rpt_mem.Email_Address as [Work Email],
    CONVERT(VARCHAR(10), Date_Time_Start, 101) as [Shift Start Date],
    MIN(CONVERT(VARCHAR(5), Date_Time_Start, 108)) as [Shift Start Time],
    CONVERT(VARCHAR(10), Date_Time_End, 101) as [Shift End Date],
    MAX(CONVERT(VARCHAR(5), Date_Time_End, 108)) as [Shift End Time]
from Schedule
where rpt_mem.Member_Full_Name = 'Member1'
AND CONVERT(VARCHAR(10), Date_Time_Start, 101) = '10/04/2020'

查询结果

如何修改查询以仅获取第一个班次开始时间和最后一个结束时间，如第二张图片

目标结果

标签： sqlsql-servertsqlgroup-by

解决方案

你也可以这样做——

select 
    rpt_mem.Member_Full_Name as Member,
    rpt_mem.Email_Address as [Work Email],
    CONVERT(VARCHAR(10), MIN(Date_Time_Start), 101) as [Shift Start Date],
    CONVERT(VARCHAR(5), MIN(Date_Time_Start), 108) as [Shift Start Time],
    CONVERT(VARCHAR(10), MAX(Date_Time_End), 101) as [Shift End Date],
    CONVERT(VARCHAR(5), MAX(Date_Time_End), 108) as [Shift End Time]
from Schedule rpt_mem
where rpt_mem.Member_Full_Name = 'Member1'
AND CONVERT(VARCHAR(10), Date_Time_Start, 101) = '04/10/2020'
GROUP BY 
 rpt_mem.Member_Full_Name,
 rpt_mem.Email_Address;

这是一个 db fiddle 链接 - https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=61da8cab5471d21818eb5b7fec8d509c

推荐阅读