因此,给定一个类似下面的表,我想获取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;

09-10 07:58
查看更多