我正在使用MySQL
具有2个表的数据库。我的目标是使用一个表给出的间隔从另一个表中选择值。在我的示例中,我想select ErrorCode WHERE Errors
。Time_Stamp is between Batch. Time_Stamp for each BatchNum
。
CREATE TABLE Batch (BatchNum INT, Time_Stamp DATETIME);
INSERT INTO Batch VALUES (1,'2020-12-17 07:29:36'), (1, '2020-12-17 08:31:56'), (1, '2020-12-17 08:41:56'), (2, '2020-12-17 09:31:13'), (2, '2020-12-17 10:00:00'), (2, '2020-12-17 10:00:57'), (2, '2020-12-17 10:01:57'), (3, '2020-12-17 10:47:59'), (3, '2020-12-17 10:48:59'), (3, '2020-12-17 10:50:59');
CREATE TABLE Errors (ErrorCode INT, Time_Stamp DATETIME);
INSERT INTO Errors VALUES (10, '2020-12-17 07:29:35'), (11, '2020-12-17 07:30:00'), (12, '2020-12-17 07:30:35'), (10, '2020-12-17 07:30:40'), (22, '2020-12-17 10:01:45'), (23, '2020-12-17 10:48:00');
我想从中选择2个:
诸如SELECT BatchNum,ErrorCode,Errors.Time_Stamp之类的内容,在Erorrs.Time_Stamp之间,批处理开始与批处理结束之间
| BatchNum | ErrorCode | Errors.Time_Stamp |
+----------+-----------+---------------------+
| 1 | 11 | 2020-12-17 07:30:00 |
| 1 | 12 | 2020-12-17 07:30:35 |
| 1 | 10 | 2020-12-17 07:30:40 |
| 2 | 22 | 2020-12-17 10:01:45 |
| 3 | 23 | 2020-12-17 10:48:00 |
+----------+-----------+---------------------+
和类似SELECT BatchNum,ErrorCode,Errors.Time_Stamp WHERE Erorrs.Time_Stamp BETWEEN特定批次开始(此处为批次1)
| ErrorCode | Errors.Time_Stamp |
+----------+-----------+----------+
| 11 | 2020-12-17 07:30:00 |
| 12 | 2020-12-17 07:30:35 |
| 10 | 2020-12-17 07:30:40 |
+----------+-----------+----------+
我在用 :
select *
from (
select e.*,
lag(BatchNum) over(order by time) lag_BatchNum
from Batch e
) e
where not lag_BatchNum <=> BatchNum
查找BatchNum
更改,但我不知道如何将此包含在另一个选择中以使ErrorCodes
不同的之间发生BatchNum
。
JOIN
我可能可以做一些事情,但是我的主要问题是如何获取时间间隔以获取错误。
谢谢你的帮助
我想你要:
select b.*, e.error_code, e.time_stamp as error_timestamp
from (
select b.*,
lead(time_stamp) over(order by time_stamp) lead_time_stamp
from batch b
) b
inner join errors e
on e.time_stamp >= b.time_stamp
and (e.time_stamp < b.lead_time_stamp or b.lead_time_stamp is null)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句