SQL（自连接）

最新推荐文章于 2023-12-11 15:43:15 发布

@北海怪兽

最新推荐文章于 2023-12-11 15:43:15 发布

阅读量164

点赞数

分类专栏：数据库文章标签： sql 数据库

本文链接：https://blog.csdn.net/gly1653810310/article/details/105791575

版权

数据库专栏收录该内容

73 篇文章 6 订阅

订阅专栏

现有一张表‘customers’记录每天商场的客流量，至少连续三天客流量超过3000则称为高峰期。

ID	DATE	AMOUNT
1	2020-4-20	2098
2	2020-4-21	1765
3	2020-4-22	3721
4	2020-4-23	3587
5	2020-4-24	4176
6	2020-4-25	2658

应该得到的查询结果：

ID	DATE	AMOUNT
3	2020-4-22	3721
4	2020-4-23	3587
5	2020-4-24	4176

select distinct C1.*
from Customers c1, Customers c2, Customers c3
where c1.AMOUNT >= 3000 and C2.AMOUNT >= 3000 and C3.AMOUNT >= 3000
and
(
   (C1.id - C2.id = 1 and C1.id - C3.id = 2 and C2.id - C3.id =1)  -- C1, C2, C3
    or
    (C2.id - C1.id = 1 and C2.id - C3.id = 2 and C1.id - C3.id =1) -- C2, C1, C3
    or
    (C3.id - C2.id = 1 and C2.id - C1.id =1 and C3.id - C1.id = 2) -- C3, C2, C1
)
order by C1.id
;