[pgsql-jp: 37597] Re: 1:n から 1:1 を抽出するSQL文

[kanout ＠ nttdata.co.jp]

かのうです。

SQL2で書くと、こんな感じです。

select ta.x,tb.y 
  from tb inner join ta
    on tb.k=ta.i
  where tb.j=
      (select max(bq.j) 
           from tb as bq
         where bq.k=tb.k);

ついつい気になって、確認しました。
大きいデータベースで、福島様のSQLとのEXPLAINを取っていただき、
フィードバックしていただけるとうれしいです。
# PostgreSQLのプランナがどの程度のものか知りたい。

ついでにいうと、最後の行の
         where bq.k=tb.k);
を
         where bq.k=ta.i);
として、アクセスプランがどうなるかも興味あります。
# 数行のデータベースじゃ、何してもインデックスを使ってくれないので。。。

---------------------------------------------------------------------

test1=# create table ta(i integer,x text);
test1=# create table tb(j integer,k integer,y text);

test1=# insert into ta values(1,'総務省');
test1=# insert into ta values(2,'金融庁');
test1=# insert into ta values(3,'防衛庁');
test1=# select * from ta;
 i |   x
---+--------
 1 | 総務省
 2 | 金融庁
 3 | 防衛庁
(3 rows)

test1=# insert into tb values(1,1,'田中');
test1=# insert into tb values(2,1,'工藤');
test1=# insert into tb values(3,2,'加藤');
test1=# insert into tb values(4,2,'柴田');
test1=# insert into tb values(5,3,'青木');
test1=# insert into tb values(6,3,'新藤');
test1=# select * from tb;
 j | k |  y
---+---+------
 1 | 1 | 田中
 2 | 1 | 工藤
 3 | 2 | 加藤
 4 | 2 | 柴田
 5 | 3 | 青木
 6 | 3 | 新藤
(6 rows)

test1=# select ta.x,tb.y from tb inner join ta on tb.k=ta.i
test1-# where tb.j=(select max(bq.j) from tb as bq where bq.k=tb.k);
   x    |  y
--------+------
 総務省 | 工藤
 金融庁 | 柴田
 防衛庁 | 新藤
(3 rows)


> -----Original Message-----
> From: pgsql-jp-bounces ＠ ml.postgresql.jp 
> [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of 
> FUKUSHIMA Katsuaki
> Sent: Friday, October 20, 2006 4:51 PM
> To: PostgreSQL Japanese Mailing List
> Subject: [pgsql-jp: 37593] Re: 1:n から 1:1 を抽出するSQL文
> 
> 福島＠現実逃避モードです。
> 
> SELECT A.X, B.Y FROM A, B, (SELECT max(J) AS J, K AS I FROM B GROUP BY
> K) AS C WHERE A.I = C.I and B.J = C.J
> 
> ではどうでしょう？(SQL89ですいません)
> 
> 
> > 今、テーブル A と B があり A:B は 1:n の関係で定義されています。具体的に
は、
> > テーブル A:
> > col I : primary key
> > col X : 会社名
> > col P : ....
> > col Q : ....
> > 
> > テーブル B:
> > col J : primary key
> > col K : references A(I)
> > col Y : 会員名
> > 
> > となっており、テーブル B には同一会社の複数の会員が登録されています。
> > ここで、ある条件に合致した会社名とその会社に属する会員のうち、最も
> > primary key (J) の値の大きいものを１名づつ抽出したいのですが、有効なSQL
> > 文は書けるでしょうか？
> > 期待する結果は、
> > 1. 株式会社A 山田
> > 2. 株式会社B 田中
> > 3. 有限会社C 鈴木
> > 
> --
> ---------------------------------------------------------
> FUKUSHIMA Katsuaki at Seino Information Service Co., Ltd.
> e-mail   kfukushima ＠ sis.seino.co.jp
>