[pgsql-jp: 40411] Re: 階層問い合わせの使い方について

[Tomoaki Sato]

佐藤です。

> 佐藤です。
> 
> 8.3 で再帰問い合わせが使えないから connectby 関数でやっているんですね。
> もうちょっと考えてみます。

スマートではないですが、自己結合すればできますね。

=# SELECT DISTINCT g.key, g.key_p, g.name
-#     FROM goods AS g, goods AS g2
-#     WHERE g.key IN (
(#             SELECT g3.key
(#                 FROM connectby('goods', 'key', 'key_p', g2.key, 0)
(#                     AS t(key text, key_p text, level int), goods AS g3
(#                 WHERE g3.key = t.key
(#         ) AND g2.name LIKE '%菓子%';
 key  | key_p |    name    
------+-------+------------
 1500 | 1000  | お菓子
 1510 | 1500  | 和菓子
 1511 | 1510  | 生和菓子
 1512 | 1510  | まんじゅう
 1513 | 1510  | ようかん
 1514 | 1510  | せんべい
 1515 | 1510  | 最中
 1520 | 1500  | 洋菓子
 1521 | 1520  | プリン
 1522 | 1520  | ゼリー
 1523 | 1520  | ケーキ
 1524 | 1520  | パイ
 1525 | 1520  | カステラ
(13 rows)

ただ、これなら 1 つの SQL にこだわらず、条件に一致するキーを取得し、キー
ごとに connectby 関数を実行したほうがいいかもしれません。

>> 佐藤です。
>> 
>>> お世話になります、大熊と申します。
>>> 
>>> 環境：PostgreSQL 8.3.7
>>> 
>>> create table goods (key char(4), key_p char(4), name text);
>>> 
>>> insert into goods (key, key_p, name) values ('1000', '0000', '食べ物');
>>> insert into goods (key, key_p, name) values ('2000', '0000', '飲み物');
>>> insert into goods (key, key_p, name) values ('3000', '0000', 'ファッション');
>>> insert into goods (key, key_p, name) values ('4000', '0000', 'インテリア');
>>> insert into goods (key, key_p, name) values ('1100', '1000', '肉');
>>> insert into goods (key, key_p, name) values ('1200', '1000', '魚');
>>> insert into goods (key, key_p, name) values ('1300', '1000', '野菜');
>>> insert into goods (key, key_p, name) values ('1400', '1000', 'くだもの');
>>> insert into goods (key, key_p, name) values ('1500', '1000', 'お菓子');
>>> insert into goods (key, key_p, name) values ('1600', '1000', 'パン');
>> 
>> ここから
>> 
>>> insert into goods (key, key_p, name) values ('1510', '1500', '和菓子');
>>> insert into goods (key, key_p, name) values ('1520', '1500', '洋菓子');
>>> insert into goods (key, key_p, name) values ('1510', '1511', '生和菓子');
>>> insert into goods (key, key_p, name) values ('1510', '1512', 'まんじゅう');
>>> insert into goods (key, key_p, name) values ('1510', '1513', 'ようかん');
>>> insert into goods (key, key_p, name) values ('1510', '1514', 'せんべい');
>>> insert into goods (key, key_p, name) values ('1510', '1515', '最中');
>>> insert into goods (key, key_p, name) values ('1520', '1521', 'プリン');
>>> insert into goods (key, key_p, name) values ('1520', '1522', 'ゼリー');
>>> insert into goods (key, key_p, name) values ('1520', '1523', 'ケーキ');
>>> insert into goods (key, key_p, name) values ('1520', '1524', 'パイ');
>>> insert into goods (key, key_p, name) values ('1520', '1525', 'カステラ');
>> 
>> ここまでの key と key_p 列の値は逆でないでしょうか。
>> 
>>> このようなデータを階層問い合わせを用いて「お菓子」以下をコードを指定
>>> して取得する場合は以下のように行いますが、
>>> 
>>> select goods.*
>>>  from connectby ( 'goods', 'key', 'key_p', '1500', 0) 
>>>  as tree (key text, key_p text, level int ), goods
>>>  where goods.key = tree.key;
>>> 
>>> コード指定ではなく、引き渡される情報が"菓子"で項目nameに対してlike検
>>> 索をかけて取得する場合はどのように行ったらよいのでしょうか？
>>> ( 'goods', 'key', 'key_p', '1500', 0) 
>> 
>> connectby 関数はほとんど使ったことがないので connectby 関数でどう書く
>> かはすぐには分かりませんが、8.4 以降であれば再帰問い合わせを使って以下
>> のように書けると思います。
>> 
>> =# WITH RECURSIVE t (key, key_p, name) AS (
>> (#     SELECT g.key, g.key_p, g.name
>> (#         FROM goods AS g WHERE g.name LIKE '%菓子%'
>> (#     UNION SELECT g.key, g.key_p, g.name
>> (#         FROM goods AS g, t WHERE g.key_p = t.key
>> (# ) SELECT t.key, t.key_p, t.name FROM t;
>>  key  | key_p |    name    
>> ------+-------+------------
>>  1500 | 1000  | お菓子
>>  1510 | 1500  | 和菓子
>>  1520 | 1500  | 洋菓子
>>  1511 | 1510  | 生和菓子
>>  1512 | 1510  | まんじゅう
>>  1513 | 1510  | ようかん
>>  1514 | 1510  | せんべい
>>  1515 | 1510  | 最中
>>  1521 | 1520  | プリン
>>  1522 | 1520  | ゼリー
>>  1523 | 1520  | ケーキ
>>  1524 | 1520  | パイ
>>  1525 | 1520  | カステラ
>> (13 rows)
>> 
>>> この部分を動的に変えたいのですが、ＳＱＬ一発では無理なのでしょうか？


----
佐藤 友章 <sato ＠ sraoss.co.jp>
SRA OSS, Inc. 日本支社
PostgreSQL トレーニング: http://www.sraoss.co.jp/prod_serv/training/
PowerGres ファミリー: http://powergres.sraoss.co.jp/