【PostgreSQL】前方一致するレコード以外のレコードを抽出するSQLクエリ

Question

実現したいこと

• 以下のテーブル（前提に記載）において、idが同じname同士を比較し、末尾2文字以外の文字列が前方一致するレコード以外のレコードを抽出する。

前提

• テーブル（以下、Table）

id	name
integer	character varying（文字数制限なし）

• 利用環境
  PostgreSQL 15.3, compiled by Visual C++ build 1914, 64-bit (Windows)

例、期待結果、現状

• 例

  id	name
  1	abcdefg0
  1	abcdefg1
  1	abcdefg
  1	xyz
  2	abcdefg
  2	xyz

• 期待結果

  id	name
  1	xyz
  2	abcdefg
  2	xyz

（注釈）id = 1において、name = 'abcdefg0', 'abcdefg1', 'abcdefg'の末尾2文字以外の文字列が前方一致するため、それら以外のレコードが抽出されることを期待する。

• 現状
  すべてのレコードが抽出されない状況（出力結果0行）

該当のソースコード

SELECT t1.id, t1.name
FROM Table t1
WHERE NOT EXISTS (
    SELECT *
    FROM Table t2
    WHERE t1.id = t2.id
    AND t1.name LIKE CONCAT(substring(t2.name, 1, length(t2.name) - 2), '%')
)
ORDER BY id;

試したこと

1. 末尾2文字以外の文字列を「substring(t2.name, 1, length(t2.name) - 2)」で表現。
2. 前方一致するかの判断を「LIKE」演算子 + 「CONCAT」関数と「%」（ワイルドカード）で表現。
3. 1, 2が一致しないものを抽出するため、「NOT EXISTS」を使用。

要旨

お恥ずかしい限りですが、「試したこと」に記載の内容へ辿り着くのに数日要しました。ここからどう変えれば良いか、何が間違っているのか分からず止まっています。
どんなことでも構いませんので、ご教示いただけたら幸いです。よろしくお願いいたします。

Comments

[hoshi-takanori]

「末尾2文字以外の文字列が前方一致」という条件が微妙に曖昧というか、substring で末尾 2 文字を取り除いた結果は 'abcdef', 'abcdef', 'abcde' なので、'abcdefg' は残ってしまうような…。

Answer 1

EXISTS句で利用しているサブクエリにT1.name <> T2.nameという条件が不足していそうです。

SELECT *
FROM T AS T1
WHERE NOT EXISTS (
  SELECT *
  FROM T AS T2
  WHERE T1.id = T2.id
  AND T1.name <> T2.name
  AND T2.name LIKE CONCAT(SUBSTRING(T1.name, 1, LENGTH(T1.name)-2), '%')
);

Comments

[k0u_maeda]

ご回答ありがとうございます。

ご指摘の通り、サブクエリ内の条件「T1.name <> T2.name」が抜けておりました。
この条件が無いと、無条件にすべてのレコードが抽出されない結果となってしまいますね。
サブクエリの理解が足りていなかったようです。。

初めて質問させていただきましたが、ご丁寧に回答いただきとてもありがたい限りです。
この度は誠にありがとうございました。