因此,给定一个类似下面的表,我想获取id
至少连续三年的行。
+---------+--------+
| id | year |
+------------------+
| 2 | 2003 |
| 2 | 2004 |
| 1 | 2005 |
| 2 | 2005 |
| 1 | 2007 |
| 1 | 2008 |
+---------+--------+
这里的结果当然是:
+---------+
| id |
+---------+
| 2 |
+---------+
任何关于如何构造查询的输入都是非常好的。
最佳答案
当您在id字段上至少有一个索引时,此项可以工作并且速度很快:
WITH t1 AS (
SELECT *
FROM (VALUES
(2,2003),
(2,2004),
(1,2005),
(2,2005),
(1,2007),
(1,2008)
) v(id, year)
)
SELECT DISTINCT t1.id
FROM t1 -- your tablename
JOIN t1 AS t2 ON t1.id = t2.id AND t1.year + 1 = t2.year
JOIN t1 AS t3 ON t1.id = t3.id AND t1.year + 2 = t3.year;