变量重叠的递归MySQL查询
变量重叠的递归MySQL查询
提问于 2019-12-03 00:27:17
我正在尝试弄清楚如何在MySQL中进行递归查询。
我有一个表，在那里我可以跟踪某人获得认证的日期。相同的认证可能会重叠--假设您在认证结束之前获得了重新认证，以保持您的认证！
以下是跟踪用户认证的表的示例。对于这个问题，user和cert表是非常基础的，没有必要。
CREATE TABLE user_cert (
id INT AUTO_INCREMENT,
user INT NOT NULL,
cert INT NOT NULL,
date_begin DATE NOT NULL,
date_end DATE NOT NULL,
PRIMARY KEY (id)
)例如，对于一个用户，这可能是值。
| id | user | cert | date_begin | date_end |
|----|------|------|------------|------------|
| 01 | 100 | 1000 | 2018-12-01 | 2019-02-01 |
| 02 | 100 | 1000 | 2019-04-01 | 2019-07-01 |
| 03 | 100 | 1000 | 2019-06-01 | 2019-09-01 |
| 04 | 100 | 1000 | 2019-08-01 | 2019-12-01 |
| 05 | 100 | 1000 | 2019-07-01 | 2019-11-01 |这是一个直观的表示。○表示每个认证期的开始日期和结束日期。●表示我希望在查询中获得的开始日期和结束日期。可以有任意数量的重叠。|表示我正在检查的日期。
id--12--01--02--03--04--05--06--07--08--09--10--11--12--
01 ○-------○ |
02 ●-----------○
03 ○-----------○
04 | ○---------------●
05 | ○---------------○很容易编写一个查询来获取与我正在检查的日期(2019-06-15)重叠的行的最小日期和最大日期。
SELECT user, MIN(date_begin), MAX(date_end)
FROM user_cert
WHERE user = 100 AND '2019-06-15' BETWEEN date_begin AND date_end
GROUP BY user;这将产生1, '2019-04-01', '2019-09-01'，因为这两行重叠。
我的问题是:是否有可能从单个查询中获取1, '2019-04-01', '2019-12-01'，以获得连续认证的最小和最大日期？如果我要多次加入user_cert表，我知道该怎么做，但那只允许这么多层的扩展。如果我不知道存在多少可能的扩展重叠怎么办？
回答 1
Stack Overflow用户
发布于 2019-12-03 03:20:40
对于mysql 8.0，请尝试以下操作：
CREATE TABLE IF NOT EXISTS user_cert (
`user` int not null,
date_begin date not null,
date_end date not null
);
INSERT user_cert(`user`,date_begin,date_end)
VALUES (100,'2018-12-01','2019-02-01')
,(100,'2019-04-01','2019-07-01')
,(100,'2019-06-01','2019-09-01')
,(100,'2019-08-01','2019-12-01')
,(100,'2019-07-01','2019-11-01')
,(101,'2019-04-01','2019-07-01')
,(101,'2019-06-01','2019-09-01')
,(101,'2019-08-01','2019-12-01')
,(101,'2019-01-01','2019-03-01');
;WITH RECURSIVE cte_r(`user`,date_begin,date_end,rid)
AS
(
SELECT `user`, date_begin, date_end, ROW_NUMBER() OVER(PARTITION BY `user` ORDER BY date_begin) AS rid
FROM user_cert
),
cte_range(`user`,date_begin, date_end, rid)
AS
(
SELECT `user`,date_begin, date_end, rid
FROM cte_r
WHERE rid=1
UNION ALL
SELECT p.`user`, CASE WHEN c.date_begin<p.date_end THEN p.date_begin ELSE c.date_begin END AS date_begin,
c.date_end,c.rid
FROM cte_range p
INNER JOIN cte_r c
ON p.`user`=c.`user`
AND p.rid+1=c.rid
)
SELECT `user`,date_begin,MAX(date_end) AS date_end FROM Cte_range GROUP BY `user`,date_begin;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59142981
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号