sql - 选择同一行中的顶行数据（如果同一列内的数据差异小于'n'小时）sql
问题描述
我有这个需要排序的现有数据，我的问题是我们如何对同一列内的数据进行排序，如果值之间的差异小于 3 小时，将选择最高值。
这合理吗？
根据下表，2019-12-25有 3 个值，
| 2019-12-25 14:00:02.000 | 2019-12-25 |
| 2019-12-25 15:39:57.000 | 2019-12-25 |
| 2019-12-25 22:39:57.000 | 2019-12-25 |
我想从列表中删除2019-12-25 14:00:02.000以便保留的数据将是2019-12-25 15:39:57.000和2019-12-25 22:39:57.000。
我试图实现的流程是，如果时间戳之间的日期时间差在 3 小时内，则只会选择最大值，在这种情况下，将从 2019 年开始选择 2019-12-25 15:39:57.000- 12-25 14:00:02.000 和 2019-12-25 15:39:57.000 在相同的 3 小时范围内，而 2019-12-25 22:39:57.000 将保留在表中，因为它的时间超过了 3 小时到 2 个值。
有没有办法让它工作？
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| badgenumber | checktype | recordout | checkdate | employeeidno | fullname | departmentname |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-21 23:43:36.000 | 2019-12-21 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-22 22:36:50.000 | 2019-12-22 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-23 18:03:16.000 | 2019-12-23 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-24 22:06:58.000 | 2019-12-24 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 14:00:02.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 15:39:57.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 22:39:57.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-26 14:00:02.000 | 2019-12-26 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-28 22:00:01.000 | 2019-12-28 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-28 23:31:11.000 | 2019-12-28 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-29 15:08:10.000 | 2019-12-29 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-30 16:03:20.000 | 2019-12-30 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2020-01-02 06:52:18.000 | 2020-01-02 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2020-01-03 08:00:57.000 | 2020-01-03 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2020-01-04 06:40:11.000 | 2020-01-04 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
例子。
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| badgenumber | checktype | recordout | checkdate | employeeidno | fullname | departmentname |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 14:00:02.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 15:39:57.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 22:39:57.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
2个时间戳之间的差异小于3小时。因此 ，将改为选择下午 3:39:57，而下午 10:39:57将被忽略并保留在表中，因为与同一列中的其他 2 个数据相比，它的时间超过了 3 小时。
这是我的预期输出。
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| badgenumber | checktype | recordout | checkdate | employeeidno | fullname | departmentname |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-21 23:43:36.000 | 2019-12-21 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-22 22:36:50.000 | 2019-12-22 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-23 18:03:16.000 | 2019-12-23 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-24 22:06:58.000 | 2019-12-24 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 14:00:02.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 15:39:57.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-25 22:39:57.000 | 2019-12-25 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-26 14:00:02.000 | 2019-12-26 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-28 22:00:01.000 | 2019-12-28 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-28 23:31:11.000 | 2019-12-28 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-29 15:08:10.000 | 2019-12-29 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2019-12-30 16:03:20.000 | 2019-12-30 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2020-01-02 06:52:18.000 | 2020-01-02 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2020-01-03 08:00:57.000 | 2020-01-03 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
| 1233 | O | 2020-01-04 06:40:11.000 | 2020-01-04 | | | |
+-------------+-----------+-------------------------+------------+--------------+----------+----------------+
这是我需要它工作的代码的一部分。
(SELECT MAX(userinfo.badgenumber) AS badgenumber, MAX(RTRIM(checkinout.checktype)) AS 'checktype',
MAX(checkinout.checktime) as 'recordout', MAX(CONVERT(date,checkinout.checktime)) as checkdate,
MAX(RTRIM(employeemasterfile.employeeidno)) AS 'employeeidno', MAX(RTRIM(employeemasterfile.lastname))+', '+
MAX(RTRIM(employeemasterfile.firstname))+' '+MAX(LEFT(employeemasterfile.middlename,1))+'.' AS 'fullname',
MAX(RTRIM(departmentmasterfile.departmentname)) AS 'departmentname' FROM ((checkinout INNER JOIN userinfo
ON checkinout.userid = userinfo.userid) INNER JOIN employeemasterfile ON userinfo.badgenumber = employeemasterfile.fingerscanno)
INNER JOIN departmentmasterfile ON LEFT(employeemasterfile.employeeidno, 4) = LEFT(departmentmasterfile.departmentcode, 4)
WHERE CONVERT(date,checkinout.checktime) BETWEEN '2019-12-21' AND DATEADD(DAY, 1,'2020-01-05') AND fingerscanno = '1233'
AND CHECKINOUT.CHECKTYPE = 'O' COLLATE SQL_Latin1_General_CP1_CS_AS GROUP BY userinfo.badgenumber, LEFT(checkinout.checktime,14)) AS t2
ON
t2.recordout BETWEEN DATEADD(HOUR,-6,t0.mergetimeoutorig) AND DATEADD(HOUR, 6,t0.mergetimeoutorig)
示例值
t0.mergetimeoutorig= 25/12/2019
解决方案
以下代码将为您提供需要排除的日期。
SELECT DS.*
FROM @DataSource DS1
CROSS APPLY
(
SELECT *
FROM @DataSource DS2
WHERE DS1.[checkdate] = DS2.[checkdate]
AND DATEDIFF(HOUR, DS1.[recordout], DS2.[recordout]) < 3
AND DS1.[recordout] < DS2.[recordout]
) DS;
然后您可以DELETE从原始表或SELECT仅需要的数据。这是完整的工作示例：
DECLARE @DataSource TABLE
(
[recordout] DATETIME
,[checkdate] DATE
);
INSERT INTO @DataSource ([recordout], [checkdate])
VALUES ('2019-12-21 23:43:36.000', '2019-12-21')
,('2019-12-22 22:36:50.000', '2019-12-22')
,('2019-12-23 18:03:16.000', '2019-12-23')
,('2019-12-24 22:06:58.000', '2019-12-24')
,('2019-12-25 01:39:57.000', '2019-12-25')
,('2019-12-25 02:39:57.000', '2019-12-25')
,('2019-12-25 02:49:57.000', '2019-12-25')
,('2019-12-25 14:00:02.000', '2019-12-25')
,('2019-12-25 15:39:57.000', '2019-12-25')
,('2019-12-25 22:39:57.000', '2019-12-25')
,('2019-12-26 14:00:02.000', '2019-12-26')
,('2019-12-28 22:00:01.000', '2019-12-28')
,('2019-12-28 23:31:11.000', '2019-12-28')
,('2019-12-29 15:08:10.000', '2019-12-29')
,('2019-12-30 16:03:20.000', '2019-12-30')
,('2020-01-02 06:52:18.000', '2020-01-02')
,('2020-01-03 08:00:57.000', '2020-01-03')
,('2020-01-04 06:40:11.000', '2020-01-04');
WITH DataSource AS
(
SELECT DS.*
FROM @DataSource DS1
CROSS APPLY
(
SELECT *
FROM @DataSource DS2
WHERE DS1.[checkdate] = DS2.[checkdate]
AND DATEDIFF(HOUR, DS1.[recordout], DS2.[recordout]) < 3
AND DS1.[recordout] < DS2.[recordout]
) DS
)
SELECT DS1.*
FROM @DataSource DS1
LEFT JOIN DataSource DS2
ON DS1.[checkdate] = DS2.[checkdate]
AND DS1.[recordout] = DS2.[recordout]
WHERE DS2.[checkdate] IS NULL
ORDER BY DS1.[recordout] ASC;
推荐阅读
- html - 在jquery ui中删除背景渐变
- javascript - 我对脚本的顺序有误，但不知道如何解决。以及如何将输入保存到变量中？
- reactjs - setstate 中的箭头函数使用
- node.js - 如何在 aws ec2 中从域调用节点 js API
- php - Paypal 支付网关无法使用 curl 请求
- reactjs - 为什么 React - Create-React-Applications 不允许符号链接？
- regex - 正则表达式使用字符拆分并用不同的字符串替换第一部分
- spring-boot - Kafka 消费者在偏移提交后无法读取所有消息（错误=OFFSET_OUT_OF_RANGE
- javascript - 当我使用 getElementsbyClassName 时，无法使用 Vanilla JS 增加 div 的宽度
- python - 调用函数会导致所需的位置参数错误