MySQL – 如何选择’DISTINCT’重叠时段(日期或数字范围)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL – 如何选择’DISTINCT’重叠时段(日期或数字范围)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3169字，纯文字阅读大概需要5分钟。
内容图文
简洁地说,如果查询告诉我A重叠B,那么我也不需要它也告诉我B也重叠A,因为它们相互重叠.
所以我试图在sql中使用自联接来选择’DISTINCT’重叠.
为了说明,这里是一个简单的SQL小提琴,我写的是为了显示包含重叠选择(http://sqlfiddle.com/#!9/7af84f/1)
详细地…
假设我有一个名称(char),d1(int),d2(int)的表,其架构如下.这里d1和d2表示某个间隔的开始和结束,该间隔可能与同一个表中的另一个间隔重叠.
CREATE TABLE test (
letter char ,
d1 int ,
d2 int
) ;
鉴于此表,我填写了一些值
INSERT INTO test (letter,d1,d2)
VALUES
('A', 2, 10), -- overlaps C and D
('B', 12, 20), -- overlaps E
('C', 5, 10), -- overlaps A and D
('D', 1, 8), -- overlaps A and C
('E', 13, 15), -- overlaps B
('F', 25, 30); -- doesn't overlap anything
并运行以下查询,该查询使用自联接来正确查找其中一行中的d1和d2与其他行中的d1和d2包含重叠的行.
-- selects all records that overlap in the range d1 - d2 inclusive
-- (excluding the implicit overlap between a record and itself)
-- The results are sorted by letter followed by d1
SELECT
basetable.letter as test_letter,
basetable.d1,
basetable.d2,
overlaptable.letter as overlap_letter,
overlaptable.d1 as overlap_d1,
overlaptable.d2 as overlap_d2
FROM
test as basetable,
test as overlaptable
WHERE
-- there is an inclusive overlap
basetable.d1 <= overlaptable.d2 and basetable.d2 >= overlaptable.d1
AND
-- the row being checked is not itsself
basetable.letter <> overlaptable.letter
AND
basetable.d1 <> overlaptable.d1
AND
basetable.d2 <> overlaptable.d2
ORDER BY
basetable.letter,
basetable.d1
这正确地给了我以下,显示了所有6个版本的重叠,例如左手栏表示A重叠C而另一行显示C重叠A(注意sqlfiddle似乎不理解字段别名所以我的列标题不同)
test_letter d1 d2 overlap_letter overlap_d1 overlap_d2
A 2 10 D 1 8
B 12 20 E 13 15
C 5 10 D 1 8
D 1 8 A 2 10
D 1 8 C 5 10
E 13 15 B 12 20
我的问题是：
如何更改sql以获得四行’DISTINCT’或’单向’重叠？
即这个结果……
test_letter d1 d2 overlap_letter overlap_d1 overlap_d2
A 2 10 D 1 8
A 2 10 C 5 10
B 12 20 E 13 15
C 5 10 D 1 8
例如：
根据以下推理,只显示左侧列中A,B和C的记录的结果
> A(2,10)与D(1,8)和C(5,10)重叠,{显示这两行}
> B(12,20)与E重叠(13,15){显示此行}
> C(5,10)与D(1,8)重叠{显示此行但不显示A(1,10)重叠,因为第2行已显示A和C重叠}
> D(1,8){不要显示我们已经知道的关于A(1,10)和C(5,10)的任何新内容
> E(13,15){不要显示我们已经知道的关于B(12,20)的任何新内容
> F(25,30){由于没有重叠,所以不要显示任何内容}
解决方法:
你可以改变不平等.而且,你还应该使用JOIN：
SELECT basetable.letter as test_letter, basetable.d1, basetable.d2,
overlaptable.letter as overlap_letter, overlaptable.d1 as overlap_d1, overlaptable.d2 as overlap_d2
FROM test basetable JOIN
test overlaptable
ON basetable.d1 <= overlaptable.d2 AND
basetable.d2 >= overlaptable.d1
WHERE basetable.letter < overlaptable.letter -- This is the change
ORDER BY basetable.letter, basetable.d1;
内容总结
以上是互联网集市为您收集整理的MySQL – 如何选择’DISTINCT’重叠时段(日期或数字范围)全部内容，希望文章能够帮你解决MySQL – 如何选择’DISTINCT’重叠时段(日期或数字范围)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。