sqlite - 查找具有相同列值的连续行范围

Question

我有一个带有增加主键的表e。示例行之一如下所示:

id | name | year | info
3  | a    | 1    | any
5  | a    | 1    | any
6  | a    | 1    | any
7  | b    | 3    | any
9  | b    | 3    | any
10 | a    | 3    | any
11 | a    | 1    | any

我想要让所有从最小行开始的连续行与最小行(id 3)具有相同的名称和年份值。 限制是我需要使用一个 SELECT 语句来获取结果。

所以在这个例子中，结果应该是 3,5,6:

3 | a | 1 | any
5 | a | 1 | any
6 | a | 1 | any

如果我们删除这三行并再次查询，我们应该得到7、9:

7 | b | 3 | any
9 | b | 3 | any

这是我当前的查询，但它不起作用。我是 SQL 新手，想知道如何简化它？

SELECT * 
FROM e AS E1, 
     (SELECT name, year FROM e WHERE id = (SELECT min(id) FROM e)) AS M1 
WHERE 
     E1.name = M1.name AND 
     E1.year = M1.year AND 
     E1.id < (SELECT min(id) 
              FROM e AS E2 
              WHERE E2.name != M1.name OR E2.year != M1.year)

Answer 1

SELECT *
FROM e
WHERE /* id > last_id_from_previous_result AND */
      id < COALESCE((SELECT min(e.id)
                     FROM e
                     JOIN (SELECT id, name, year
                           FROM e
                           /* WHERE id > last_id_from_previous_result */
                           ORDER BY id
                           LIMIT 1) AS first
                       ON /* e.id > first.id AND */
                          (e.name != first.name OR
                           e.year != first.year)),
                    'infinite')

最里面的查询搜索要返回的第一个 ID。

中间查询搜索不属于结果的第一行，即具有较大 ID 且具有不同 name 或 year 的第一行。在表的末尾，其结果为NULL；然后 COALESCE 返回一个大于任何数字的值。

然后，最外面的查询返回该行之前的所有行。

如果您不想删除所有先前的行，则可以添加注释掉的检查以在上一个查询的最后一个 ID 之后继续。 (一开始，请使用小于所有 ID 的值，例如 -1。)