[pgsql-jp: 26120] Re: ３つ以上のテーブルを無条件に結合する方法

[WADA Hiroyuki]

和田＠ど根性です。

ichikawa ＠ is.ocha.ac.jp wrote:
> 
> 市川です．

>   insert t3 values (7, 'a')
> 
> とかをした後には，
> 
>  count | i | j | i | j | i | j
> -------+---+---+---+---+---+---
>      1 | 0 | a | 0 | c | 3 | c
>      2 | 1 | b | 2 | b | 5 | d
>        |   |   | 4 | a |   |
>        |   |   |   |   | 7 | a
> (4 rows)
> 
> となってしまいます．つまり，t1.count=t3.count を使うか，
> t2.count=t3.count を使うのかを，t1 と t3 の
> タプル数に応じて切り換えないとだめです．

いきなり３つJOINせず、最初の２つをJOINしたものに３つめをJOINする。
countがNULLにならないようにcoalesceを使う。
これでいけるでしょう。

SELECT coalesce(t12.count,t3c.count) AS count,
    t1i,t1j,t2i,t2j,t3c.i,t3c.j FROM
(SELECT coalesce(t1c.count,t2c.count) AS count,
    t1c.i AS t1i,t1c.j AS t1j,t2c.i AS t2i,t2c.j AS t2j FROM
 (SELECT count(*),t1a.* FROM t1 AS t1a, t1 AS t1b
  WHERE t1a.i >= t1b.i
  GROUP BY t1a.i,t1a.j
  ORDER BY t1a.i 
 ) AS t1c
 FULL JOIN
 (SELECT count(*),t2a.* FROM t2 AS t2a, t2 AS t2b
  WHERE t2a.i >= t2b.i
  GROUP BY t2a.i,t2a.j
  ORDER BY t2a.i 
 ) AS t2c
 ON(t1c.count=t2c.count)
) AS t12
FULL JOIN
(SELECT count(*),t3a.* FROM 
 t3 AS t3a, t3 AS t3b
 WHERE t3a.i >= t3b.i
 GROUP BY t3a.i,t3a.j
 ORDER BY t3a.i 
) AS t3c
ON(t12.count=t3c.count)

同じ名前のカラムがあるのでASが増えて長くなってしまいましたが。