我的表结构是这样的
id date Time userid
17 2017-01-22 05:08:09.0000000 836232017
16 2017-01-22 05:08:05.0000000 836232017
11 2017-01-22 05:06:40.0000000 836232017
10 2017-01-22 05:06:38.0000000 836232017
7 2017-01-22 05:06:31.0000000 836232017
我需要获取相应用户 ID 和日期的第一个和最后一个值之间的时差。例如:用户 ID 836232017的日期差异介于 '05:08:09.0000000' 和'05:06:31.0000000'。请为此提供解决方案。
试试这个
SELECT userid,
DATEADD(SECOND, - DATEDIFF(SECOND, MAX(times), MIN(times)), @null)
FROM table
group by userid
或者这个
SELECT userid,
DATEDIFF(SECOND, Min(times), Max(times))
FROM table
group by userid
SELECT
Datediff (day,
(SELECT TOP(1) Date from dbo.YourTable),
(SELECT TOP(1) Date from dbo.YourTable ORDER BY id DESC))
因此,您可以选择分钟或小时或其他任何时间,而不是一天,请参阅此链接:https://www.w3schools.com/sql/func_datediff.asp
您唯一需要添加的是要选择的WHERE ID= the ID
和相应的日期,如下所示:
SELECT
Datediff (minute,
(SELECT TOP(1) Time from dbo.YourTable WHERE userID=123 AND date='yourdate'),
(SELECT TOP(1) Time from dbo.YourTable WHERE userID=123 AND date='yourdate' ORDER BY Time DESC))
而不是 123,而是指定要查找的 ID