如何从每个组中获取最小和最大记录 [英] How to get min and max record from each group

问题描述

虚拟餐桌



DECLARE @tb TABLE（UserId INT，InOutDateTime DATETIME，AttendanceType CHAR（1））



INSERT INTO @tb VALUES（1，'2017-08-18 08：00：00.000'，'I'）

INSERT INTO @tb VALUES（1，'2017-08- 18 07：00：00.000'，'我'）

INSERT INTO @tb VALUES（1，'2017-08-18 11：00：00.000'，'O'）

INSERT INTO @tb VALUES（1，'2017-08-18 23：00：00.000'，'I'）



INSERT INTO @tb VALUES（ 1，'2017-09-18 06：00：00.000'，'我'）

INSERT INTO @tb VALUES（1，'2017-09-18 22：00：00.000'，'O '）

INSERT INTO @tb VALUES（1，'2017-09-18 19：00：00.000'，'我'）



插入@tb VALUES（3，'2017-08-18 09：00：00.000'，'我'）

插入@tb VALUES（3，'2017-08-18 15： 00：00.000'，'O'）

INSERT INTO @tb VALUES（3，'2017-08-18 13：00：00.000'，'O'）

插入@tb VALUES（3，'2017-08-18 23：00：00.000'，'我'）



select * from @tb



我需要每个日期的每个用户的最小InOutDateTime和最大InOutDateTime。最小InOutDateTime应该来自AttendanceType ='I'。

同样最大的InOutDateTime应该来自于AttendanceType ='O'



我的尝试：



选择UserId，min（InOutDateTime）min_InDateTime，max（InOutDateTime）max_OutDateTime

来自@tb group by UserId，CAST（InOutDateTime AS DATE）

ORDER BY UserId，CAST（InOutDateTime AS DATE）



但是我无法根据AttendanceType

Dummy table

DECLARE @tb TABLE(UserId INT, InOutDateTime DATETIME , AttendanceType CHAR(1))

INSERT INTO @tb VALUES (1,'2017-08-18 08:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-08-18 07:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-08-18 11:00:00.000','O')
INSERT INTO @tb VALUES (1,'2017-08-18 23:00:00.000','I')

INSERT INTO @tb VALUES (1,'2017-09-18 06:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-09-18 22:00:00.000','O')
INSERT INTO @tb VALUES (1,'2017-09-18 19:00:00.000','I')

INSERT INTO @tb VALUES (3,'2017-08-18 09:00:00.000','I')
INSERT INTO @tb VALUES (3,'2017-08-18 15:00:00.000','O')
INSERT INTO @tb VALUES (3,'2017-08-18 13:00:00.000','O')
INSERT INTO @tb VALUES (3,'2017-08-18 23:00:00.000','I')

select * from @tb

I need minimum InOutDateTime and maximum InOutDateTime for each user for each date. Minimum InOutDateTime should be from where AttendanceType='I' .
Similarly maximum InOutDateTime should be from where AttendanceType='O'

What I have tried:

select UserId, min(InOutDateTime) min_InDateTime, max(InOutDateTime) max_OutDateTime
from @tb group by UserId, CAST(InOutDateTime AS DATE)
ORDER BY UserId, CAST(InOutDateTime AS DATE)

But i am not able to get the results based on AttendanceType

推荐答案

当您使用GROUP BY时，您可能需要使用HAVING子句：在同一查询中使用HAVING和WHERE子句Microsoft Docs [ ^ ]

When you GROUP BY, you probably need to use a HAVING clause: Use HAVING and WHERE Clauses in the Same Query | Microsoft Docs[^]

尝试这样的事情：

Try something like this:

SELECT
    UserId,
    Convert(date, InOutDateTime) As day,
    Min(CASE
        WHEN AttendanceType = 'I' THEN CAST(InOutDateTime As time(0))
        ELSE CAST('23:59:59' As time(0))
    END) As MinInTime,
    Max(CASE
        WHEN AttendanceType = 'O' THEN CAST(InOutDateTime As time(0)) 
    END) As MaxOutTime
FROM
    @tb
GROUP BY
    UserId,
    Convert(date, InOutDateTime)
;

输出：

UserId  day          MinInTime  MaxOutTime
1       2017-08-18   07:00:00   11:00:00
3       2017-08-18   09:00:00   15:00:00
1       2017-09-18   06:00:00   22:00:00

这篇关于如何从每个组中获取最小和最大记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！