【SQL】EXISTSで共通点があるレコードに対してT/Fを割り振る

Question

初投稿になります。自分で考えながらやっておりますが、
とある雑誌で当サイトの事を知り、ものは試しと投稿してみました。
自分の作業用メモをそのまま記載するため、至らないところあるかと思いますが、
皆様の知恵をお借りしたいです。どうぞよろしくお願いいたします。

TABLE S の
hogeカラムに以下のパターンで値が入っている。
先頭のT/Fは期待値。

【パターン１】
T 0001
T 00010002
T 000100020001
T 000100020002
T 00010003
T 000100030001
F 0003

【パターン２】
T 00010002
T 000100020001
T 000100020002
T 00010003
T 000100030001
F 00010004
F 00020002

【パターン３】
F 000100020001
F 000100020002
T 00010003
T 000100030001

【パターン４】
F 000100020001
F 000100020002
F 000100030001
F 000100030002

【パターン５】
T 0001
T 000100020002
T 000100030001

【パターン５】
T 0001
T 0001
F 000300030001
T 0004
T 000400030001

補足：
１．4桁,8桁,12桁の３パターンしかない。
２．hogeカラムは文字列列で昇順になっている。
３．プライマリーのシーケンスナンバーが各レコードに割り振られている。
（なので、自分自身を含めない。ということも出来る）

やりたいこと：
「１～４桁目が祖」、「５～８桁が親」、「９～１２桁が子」と仮定すると、
祖や親から見て、自分の子孫に同族がいる場合は、自分と相手双方ともTrue。
その他はFalse。を抽出したい。
（EXISTS以外の実現方法や、EXISTSの中のWHERE句のアイデアをいただきたいです）

現状：
SELECTの中でEXISTSを使って....SとEXISTSで参照してるSテーブルをS2と別名をつけて、、、、
と試行錯誤しているもののピンとくる条件（WHERE句）がない。（実力不足）

Comments

[退会済みユーザー]

余談ですが、この構造だといちいち比較のために文字列の切り出し処理を行わないといけないのでクエリー実行速度が遅そうな気がします。単純比較できるように4桁ごとに分割したカラムになるよう構造を見なおしたほうがいいかもしれません。

[KageChanMan]

＞m6u 様
ご回答くださいまして、ありがとうございます。
またアドバイスもくださり、助かります。大きなプロジェクトの根底のデータベースのため、影響が計り知れず、やむを得ずこのような構成になっております....

[退会済みユーザー]

データが膨大になるようであれば、「式に対するインデックス」を設定して速度低下を少しでも抑える工夫ができます。9.5のドキュメント→http://www.postgresql.jp/document/9.5/html/indexes-expressional.html

Answer 1

CASE式のススメの 6.テーブル同士のマッチング
http://www.geocities.jp/mickindex/database/db_case.html#LocalLink-match
を見ながら、Oracle11gR2で作ってみました

with t(Val) as(
select '0001'         from dual union all
select '00010002'     from dual union all
select '000100020001' from dual union all
select '000100020002' from dual union all
select '00010003'     from dual union all
select '000100030001' from dual union all
select '0003'         from dual)
select Val,
case (select count(*) from t b
       where substr(b.Val,1,Least(Length(b.Val),Length(a.Val)))
           = substr(a.Val,1,Least(Length(b.Val),Length(a.Val)))
         and RowNum <= 2)
when 1 then 'F' else 'T' end as TF
  from t a
order by Val;

Val           TF
------------  --
0001          T
00010002      T
000100020001  T
000100020002  T
00010003      T
000100030001  T
0003          F

Answer 2

PostgreSQL使ったことないのでこちらで関数を調べながらクエリを書いてみました。
実行していないのでそのまま通るかわかりませんが、こういうクエリではどうですか？

select 
case (A.x + B.x + C.x)
when 1 then 'F'
else 'T'
END
,A.hoge 
,B.hoge 
,C.hoge 
from (select case when hoge is null then 0 else 1 end x, hoge from Hoge where char_length(hoge) = 4) A
cross join (select case when hoge is null then 0 else 1 end x, hoge from Hoge where char_length(hoge) = 8) B on (A.hoge = substring(B.hoge,1,4))
cross join (select case when hoge is null then 0 else 1 end x, hoge from Hoge where char_length(hoge) = 12) C on (A.hoge = substring(B.hoge,1,4) OR (B.hoge = substring(C.hoge,1,8)))
;

Comments

[KageChanMan]

＞rysh様
ご回答くださいまして、ありがとうございました。
cross joinというものは存じあげず、勉強になりました。
引き続き、またご縁がございましたら、ご回答のほどお願いいたします。