MySQL查找多ID列中的空白
MySQL查找多ID列中的空白
提问于 2016-04-30 14:35:54
我想在下表中找出差距：
create table sequence
(
`Id` int,
`Value` int not null,
PRIMARY KEY (`Id`,`Value`)
);
insert into sequence
( `Id`, `Value` )
values
(10, 0 ),
(10, 1 ),
(10, 4 ),
(10, 5 ),
(10, 6 ),
(10, 7 ),
(11, 0 ),
(11, 1 ),
(11, 2 ),
(11, 5 ),
(11, 7 );预期的结果是：
10 | 2-3
11 | 3-4
11 | 6或
10 | 2
10 | 3
11 | 3
11 | 4
11 | 6我知道，colum‘值’的值在0到7之间。
可以用MySQL来做吗？
编辑1
根据我的回答，我认为：
SELECT Tbl1.Id,
startseqno,
Min(B.Value) - 1 AS END
FROM (SELECT Id,
Value + 1 AS StartSeqNo
FROM SEQUENCE AS A
WHERE NOT EXISTS (SELECT *
FROM SEQUENCE AS B
WHERE B.Id = A.id
AND B.Value = A.Value + 1)
AND Value < (SELECT Max(Value)
FROM SEQUENCE B
WHERE B.Id = A.Id)) AS Tbl1,
SEQUENCE AS B
WHERE B.Id = Tbl1.Id
AND B.Value > Tbl1.startseqno 但现在我得到了
10 | 2 | 3拜托，有人知道怎么修吗？
木琴
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-04-30 14:57:26
你可以用not exists来做这件事
select s.*
from sequence s
where not exists (select 1 from sequence s2 where s2.id = s.id and s2.value = s.value + 1) and
exists (select 1 from sequence s2 where s2.id = s.id and s2.value > s.value);exists子句很重要，因此不报告每个id的最终值。
编辑：
这里有一个更好的方法：
select s.value + 1 as startgap,
(select min(s2.value) - 1 from sequence s2 where s2.id = s.id and s2.value > s.value) as endgap
from sequence s
where not exists (select 1 from sequence s2 where s2.id = s.id and s2.value = s.value + 1) and
exists (select 1 from sequence s2 where s2.id = s.id and s2.value > s.value);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36956178
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号