

以下のようなusersテーブルがあるとします。

id | name  
---|---  
1 | Alice  
2 | Bob  
3 | Bob  
4 | Carol  
5 | Carol  
6 | Alice  
7 | Dave  
  
このテーブルからnameが同じで、idが連続していないレコードを抽出したいです。  
期待する結果は以下の通りです。

id | name  
---|---  
1 | Alice  
6 | Alice  
  
そのためのクエリや、考え方をご教示いただけますと幸いです。


CC BY-SA 4.0
2023-02-05T05:52:41.950
93707
2023-02-05T18:20:24.773
2023-02-05T06:59:11.417
4236
41605
question
sql
あるカラムの値が同じで、idが連続していないレコードの抽出について


「idが連続していないレコードを抽出したい」をより具体的に表現を改めればわかります。  
つまり、「あるレコードに対して、nameは一致しid+1となるレコードが存在しない、かつnameは一致しid-1となるレコードが存在しない」が抽出条件なはずです。  
そのまま素直にSQLとして記述すれば（テーブル名を`t`とします）、

[code]

    SELECT id, name
    FROM t as t0
    WHERE
      -- nameは一致しid+1となるレコードが存在しない
      NOT EXISTS(SELECT * FROM t as t1 WHERE t0.id + 1 = t1.id AND t0.name = t1.name)
    AND 
      -- nameは一致しid-1となるレコードが存在しない
      NOT EXISTS(SELECT * FROM t as t2 WHERE t0.id - 1 = t2.id AND t0.name = t2.name)
    
[/code]

もう少し効率をよくするなら

[code]

    SELECT id, name
    FROM t as t0
    WHERE NOT EXISTS(
        SELECT *
        FROM t as t1
        WHERE (t0.id + 1 = t1.id OR t0.id - 1 = t1.id) AND t0.name = t1.name
    )
    
[/code]


CC BY-SA 4.0
2023-02-05T06:58:52.690
93709
2023-02-05T06:58:52.690
4236
93707
answer


MySQL 8.0 以降であれば CTE(Common Table Expressions) とウィンドウ関数を利用して以下の様に書くことができます。

[code]

    WITH users_grouped AS (
        SELECT id, LAG(id) OVER w AS rb, LEAD(id) OVER w AS rn, name
        FROM users
        WINDOW w AS (PARTITION BY name ORDER BY id)
    )
    SELECT id, name FROM users_grouped WHERE id - rb != 1 OR rn - id != 1;
    
[/code]

id | name  
---|---  
1 | Alice  
6 | Alice  
  
[View on DB Fiddle](https://www.db-fiddle.com/f/3vLJMcTE9MEgQ4oWsJibWA/1)


CC BY-SA 4.0
2023-02-05T18:20:24.773
93719
2023-02-05T18:20:24.773
47127
93707
answer