sql - 使用 LAG() 检查以前的时间表中是否存在现有输入
问题描述
我之前问过这个问题,不幸的是我不清楚我的问题的细节,所以我将简化我之前的问题:
我们如何做到这一点LAG()
?由于价值,我在处理这个问题时遇到了问题NULL
。基本上我想要实现的是prevrecordin和prevrecordout列将根据recordin和recordout显示前一天的日志或日期前的时间表。
这是期望的结果。
+-------------+--------------+------------+-------------+--------------------+--------------------+--------------------+--------------------+
| badgenumber | scheduledate | schedulein | scheduleout | recordin | recordout | prevrecordin | prevrecordout |
+-------------+--------------+------------+-------------+--------------------+--------------------+--------------------+--------------------+
| 10 | 2019-12-21 | 6:00:00 AM | 2:00:00 PM | 2019-12-21 5:18 am | 2019-12-21 2:12 pm | NULL | NULL |
| 10 | 2019-12-23 | 8:00:00 AM | 5:00:00 PM | 2019-12-23 7:35 am | 2019-12-23 5:03 pm | 2019-12-21 5:18 am | 2019-12-21 2:12 pm |
| 10 | 2019-12-24 | 8:00:00 AM | 5:00:00 PM | NULL | NULL | 2019-12-23 7:35 am | 2019-12-23 5:03 pm |
| 10 | 2019-12-25 | 8:00:00 AM | 5:00:00 PM | NULL | NULL | NULL | NULL |
| 10 | 2019-12-26 | 8:00:00 AM | 5:00:00 PM | 2019-12-26 7:48 am | 2019-12-26 5:05 pm | NULL | NULL |
| 10 | 2019-12-27 | 8:00:00 AM | 5:00:00 PM | 2019-12-27 7:41 am | 2019-12-27 5:02 pm | 2019-12-26 7:48 am | 2019-12-26 5:05 pm |
| 10 | 2019-12-28 | 8:00:00 AM | 5:00:00 PM | 2019-12-28 7:35 am | 2019-12-28 5:07 pm | 2019-12-27 7:41 am | 2019-12-27 5:02 pm |
| 10 | 2019-12-30 | 8:00:00 AM | 5:00:00 PM | NULL | NULL | 2019-12-28 7:35 am | 2019-12-28 5:07 pm |
| 10 | 2019-12-31 | 8:00:00 AM | 5:00:00 PM | NULL | NULL | NULL | NULL |
| 10 | 2020-01-01 | 8:00:00 AM | 5:00:00 PM | NULL | NULL | NULL | NULL |
| 10 | 2020-01-02 | 8:00:00 AM | 5:00:00 PM | 2020-01-02 7:41 am | 2020-01-02 5:16 pm | NULL | NULL |
| 10 | 2020-01-03 | 8:00:00 AM | 5:00:00 PM | 2020-01-03 7:50 am | 2020-01-03 5:05 pm | 2020-01-02 7:41 am | 2020-01-02 5:16 pm |
| 10 | 2020-01-04 | 8:00:00 AM | 5:00:00 PM | 2020-01-04 7:41 am | 2020-01-04 5:04 pm | 2020-01-03 7:50 am | 2020-01-03 5:05 pm |
+-------------+--------------+------------+-------------+--------------------+--------------------+--------------------+--------------------+
这是我得到的结果。
SQL小提琴
http://sqlfiddle.com/#!18/ef8d9/1
CREATE TABLE Table1
([badgenumber] int, [scheduledate] varchar(10), [schedulein] varchar(10), [scheduleout] varchar(10), [recordin] DATETIME, [recordout] DATETIME)
;
INSERT INTO Table1
([badgenumber], [scheduledate], [schedulein], [scheduleout], [recordin], [recordout])
VALUES
(10, '2019-12-21', '6:00:00 AM', '2:00:00 PM', '2019-12-21 5:18 am', '2019-12-21 2:12 pm'),
(10, '2019-12-23', '8:00:00 AM', '5:00:00 PM', '2019-12-23 7:35 am', '2019-12-23 5:03 pm'),
(10, '2019-12-24', '8:00:00 AM', '5:00:00 PM', NULL, NULL),
(10, '2019-12-25', '8:00:00 AM', '5:00:00 PM', NULL, NULL),
(10, '2019-12-26', '8:00:00 AM', '5:00:00 PM', '2019-12-26 7:48 am', '2019-12-26 5:05 pm'),
(10, '2019-12-27', '8:00:00 AM', '5:00:00 PM', '2019-12-27 7:41 am', '2019-12-27 5:02 pm'),
(10, '2019-12-28', '8:00:00 AM', '5:00:00 PM', '2019-12-28 7:35 am', '2019-12-28 5:07 pm'),
(10, '2019-12-30', '8:00:00 AM', '5:00:00 PM', NULL, NULL),
(10, '2019-12-31', '8:00:00 AM', '5:00:00 PM', NULL, NULL),
(10, '2020-01-01', '8:00:00 AM', '5:00:00 PM', NULL, NULL),
(10, '2020-01-02', '8:00:00 AM', '5:00:00 PM', '2020-01-02 7:41 am', '2020-01-02 5:16 pm'),
(10, '2020-01-03', '8:00:00 AM', '5:00:00 PM', '2020-01-03 7:50 am', '2020-01-03 5:05 pm'),
(10, '2020-01-04', '8:00:00 AM', '5:00:00 PM', '2020-01-04 7:41 am', '2020-01-04 5:04 pm')
;
询问。
SELECT *, (case when datediff(day,
lag(recordin) over (partition by badgenumber order by recordin),
recordin
) >= 1
then lag(recordin) over (partition by badgenumber order by recordin)
else null
end) as prevrecordin,
(case when datediff(day,
lag(recordout) over (partition by badgenumber order by recordout),
recordout
) >= 1
then lag(recordout) over (partition by badgenumber order by recordout)
else null
end) as prevrecordout FROM Table1 ORDER BY scheduledate
这是我要修复的查询。来自https://stackoverflow.com/users/1144035/gordon-linoff关于我之前的问题检查前一天是否有现有记录
....
(case when datediff(day,
lag(recordin) over (partition by badgenumber order by recordin),
recordin
) >= 1
then lag(recordin) over (partition by badgenumber order by recordin)
else null
end) as prevrecordin,
(case when datediff(day,
lag(recordout) over (partition by badgenumber order by recordout),
recordout
) >= 1
then lag(recordout) over (partition by badgenumber order by recordout)
else null
end) as prevrecordout,
....
我也找不到解决这个问题的方法,因为有些日程安排在第二天或不到 24 小时/1 天,我打算使用scheduleate作为prevrecordin和prevrecordout的参数,有没有怎么做?
这是桌子。
+-------------+--------------+------------+-------------+--------------------+--------------------+
| badgenumber | scheduledate | schedulein | scheduleout | recordin | recordout |
+-------------+--------------+------------+-------------+--------------------+--------------------+
| 10 | 21/12/2019 | 6:00:00 AM | 2:00:00 PM | 21/12/2019 5:18 am | 21/12/2019 2:12 pm |
| 10 | 23/12/2019 | 8:00:00 AM | 5:00:00 PM | 23/12/2019 7:35 am | 23/12/2019 5:03 pm |
| 10 | 24/12/2019 | 8:00:00 AM | 5:00:00 PM | NULL | NULL |
| 10 | 25/12/2019 | 8:00:00 AM | 5:00:00 PM | NULL | NULL |
| 10 | 26/12/2019 | 8:00:00 AM | 5:00:00 PM | 26/12/2019 7:48 am | 26/12/2019 5:05 pm |
| 10 | 27/12/2019 | 8:00:00 AM | 5:00:00 PM | 27/12/2019 7:41 am | 27/12/2019 5:02 pm |
| 10 | 28/12/2019 | 8:00:00 AM | 5:00:00 PM | 28/12/2019 7:35 am | 28/12/2019 5:07 pm |
| 10 | 30/12/2019 | 8:00:00 AM | 5:00:00 PM | NULL | NULL |
| 10 | 31/12/2019 | 8:00:00 AM | 5:00:00 PM | NULL | NULL |
| 10 | 01/01/2020 | 8:00:00 AM | 5:00:00 PM | NULL | NULL |
| 10 | 02/01/2020 | 8:00:00 AM | 5:00:00 PM | 02/01/2020 7:41 am | 02/01/2020 5:16 pm |
| 10 | 03/01/2020 | 8:00:00 AM | 5:00:00 PM | 03/01/2020 7:50 am | 03/01/2020 5:05 pm |
| 10 | 04/01/2020 | 8:00:00 AM | 5:00:00 PM | 04/01/2020 7:41 am | 04/01/2020 5:04 pm |
+-------------+--------------+------------+-------------+--------------------+--------------------+
解决方案
只需创建一个增量 id,以便您可以使用自联接获取上一行信息:
WITH CTE AS (
SELECT *, row_number() over (partition by badgenumber order by scheduledate) as rn
FROM Table1
)
SELECT C1.* , C2.recordin as prevrecordin
, C2.recordout as prevrecordout
FROM CTE C1
LEFT JOIN CTE C2
ON C1.rn = C2.rn+1
AND C1.badgenumber = c2.badgenumber
;
输出
| badgenumber | scheduledate | schedulein | scheduleout | recordin | recordout | rn | prevrecordin | prevrecordout |
|-------------|--------------|------------|-------------|----------------------|----------------------|----|----------------------|----------------------|
| 10 | 2019-12-21 | 6:00:00 AM | 2:00:00 PM | 2019-12-21T05:18:00Z | 2019-12-21T14:12:00Z | 1 | (null) | (null) |
| 10 | 2019-12-23 | 8:00:00 AM | 5:00:00 PM | 2019-12-23T07:35:00Z | 2019-12-23T17:03:00Z | 2 | 2019-12-21T05:18:00Z | 2019-12-21T14:12:00Z |
| 10 | 2019-12-24 | 8:00:00 AM | 5:00:00 PM | (null) | (null) | 3 | 2019-12-23T07:35:00Z | 2019-12-23T17:03:00Z |
| 10 | 2019-12-25 | 8:00:00 AM | 5:00:00 PM | (null) | (null) | 4 | (null) | (null) |
| 10 | 2019-12-26 | 8:00:00 AM | 5:00:00 PM | 2019-12-26T07:48:00Z | 2019-12-26T17:05:00Z | 5 | (null) | (null) |
| 10 | 2019-12-27 | 8:00:00 AM | 5:00:00 PM | 2019-12-27T07:41:00Z | 2019-12-27T17:02:00Z | 6 | 2019-12-26T07:48:00Z | 2019-12-26T17:05:00Z |
| 10 | 2019-12-28 | 8:00:00 AM | 5:00:00 PM | 2019-12-28T07:35:00Z | 2019-12-28T17:07:00Z | 7 | 2019-12-27T07:41:00Z | 2019-12-27T17:02:00Z |
| 10 | 2019-12-30 | 8:00:00 AM | 5:00:00 PM | (null) | (null) | 8 | 2019-12-28T07:35:00Z | 2019-12-28T17:07:00Z |
| 10 | 2019-12-31 | 8:00:00 AM | 5:00:00 PM | (null) | (null) | 9 | (null) | (null) |
| 10 | 2020-01-01 | 8:00:00 AM | 5:00:00 PM | (null) | (null) | 10 | (null) | (null) |
| 10 | 2020-01-02 | 8:00:00 AM | 5:00:00 PM | 2020-01-02T07:41:00Z | 2020-01-02T17:16:00Z | 11 | (null) | (null) |
| 10 | 2020-01-03 | 8:00:00 AM | 5:00:00 PM | 2020-01-03T07:50:00Z | 2020-01-03T17:05:00Z | 12 | 2020-01-02T07:41:00Z | 2020-01-02T17:16:00Z |
| 10 | 2020-01-04 | 8:00:00 AM | 5:00:00 PM | 2020-01-04T07:41:00Z | 2020-01-04T17:04:00Z | 13 | 2020-01-03T07:50:00Z | 2020-01-03T17:05:00Z |
编辑:
SELECT C1.*
, lag(recordin) over (partition by badgenumber order by scheduledate) as prevrecordin
, lag(recordout) over (partition by badgenumber order by scheduledate) as prevrecordout
FROM Table1 C1;
推荐阅读
- python - The pygame code and the error is mentioned below:
- javascript - Regex prevent matching special charakter at end. \S
- flutter - How can I access an instance of an object in different classes / pages without a widget tree context?
- r - Fastest way to calculate minimum distance from a large set of locations to facilties in R/sf
- excel - Power Query with data validation: Change source data from a list (drop down) based on value
- r - 来自 Dunnett 秩检验的成对相关性
- facebook-opengraph - Facebook 调试器看不到我的 Open Graph 代码
- sorting - 将日期排序为第二个标准会给出错误的顺序
- python - chart_type 未在我的 if 语句中注册
- python - 带有 if 语句的 Python Numba jit 函数