22,302
社区成员




SELECT PersonID ,
CAST(INTime AS Date) AS Date ,
MIN(CASE WHEN IsIN = 1 THEN INTime
END) AS MinINTime ,
MAX(CASE WHEN IsIN = 0 THEN INTime
END) AS MaxINTime
FROM TableName
GROUP BY PersonID ,
CAST(INTime AS Date)
SELECT PersonID ,
CONVERT(VARCHAR(10), INTime, 120) AS INTime ,
MAX(CASE WHEN IsIN = 1 THEN INTime
END) AS MinINTime ,
MAX(CASE WHEN IsIN = 0 THEN INTime
END) AS MaxINTime
FROM TableName
GROUP BY PersonID ,
CONVERT(VARCHAR(10), INTime, 120)
;WITH T (PersonID,INTime,IsIN) AS
(
SELECT 1,'2016-09-07 08:12:00',1 UNION ALL
SELECT 1,'2016-09-07 17:42:00',0 UNION ALL
SELECT 1,'2016-09-08 09:11:00',1 UNION ALL
SELECT 1,'2016-09-08 18:02:00',0 UNION ALL
SELECT 1,'2016-09-08 19:12:00',0
)
SELECT
PersonID,
CONVERT(CHAR(10),INTime,120) AS [date],
MAX(INTime) AS maxtime,
MIN(INTime) AS mintime
FROM T
GROUP BY
PersonID,
CONVERT(CHAR(10),INTime,120)