[pgsql-jp: 30918] Re: レコード内容から列名を取得・生成？するSQL

[Akira Taniguchi]

島田さんへ

UNIX(Linux)環境でしたら、以下のようにSQLのテストが手軽に出来ますよ！

vi などで test.sql(例として) に SQL を書き込む。
psql -U userid database -f test.sql として SQL をテスト。


このクエリのポイントは、fk_table を number 別に分解する(見せかける)点です。

> サイミです。
>
> そっか、外部結合を使って
>
> SELECT m.name AS name,
>        j1.yuubin1 AS yuubin1, j1.text1 AS text1,
>        j2.yuubin2 AS yuubin2, j2.text2 AS text2,
>        j3.yuubin3 AS yuubin3, j3.text3 AS text3
> FROM   master m
> LEFT OUTER JOIN (
>     SELECT fk_table.fk_master AS master,
>            location.yuubin    AS yuubin1,
>            fk_table.text      AS text1
>     FROM fk_table, location
>     WHERE fk_table.number = 1
>       AND fk_table.location = location.id) AS j1
>   ON m.id = j1.master
> LEFT OUTER JOIN (
>     SELECT fk_table.fk_master AS master,
>            location.yuubin    AS yuubin2,
>            fk_table.text      AS text2
>     FROM fk_table, location
>     WHERE fk_table.number = 2
>       AND fk_table.location = location.id) AS j2
>   ON m.id = j2.master
> LEFT OUTER JOIN (
>     SELECT fk_table.fk_master AS master,
>            location.yuubin    AS yuubin3,
>            fk_table.text      AS text3
>     FROM fk_table, location
>     WHERE fk_table.number = 3
>       AND fk_table.location = location.id) AS j3
>   ON m.id = j3.master
> ORDER BY m.name;
>
> でいいのかな？

テスト無しでここまで書けるとは、素晴らしいですね。
わかりやすいし、ポイントつかんでますね。
サブクエリも外部結合にしたほうが実践的(エラーデータも表示する)です。

サブクエリ外部結合 & デバッグ

SELECT m.name AS name,
       j1.yuubin1 AS yuubin1, j1.text1 AS text1,
       j2.yuubin2 AS yuubin2, j2.text2 AS text2,
       j3.yuubin3 AS yuubin3, j3.text3 AS text3
FROM   master m
LEFT OUTER JOIN (
    SELECT fk_table.fk_master AS master,
           location.yuubin    AS yuubin1,
           fk_table.text      AS text1
    FROM fk_table
    LEFT OUTER JOIN location
    ON fk_table.fk_location = location.id
    WHERE fk_table.number = 1
    ) AS j1
    ON m.id = j1.master
LEFT OUTER JOIN (
    SELECT fk_table.fk_master AS master,
           location.yuubin    AS yuubin2,
           fk_table.text      AS text2
    FROM fk_table
    LEFT OUTER JOIN location
    ON fk_table.fk_location = location.id
    WHERE fk_table.number = 2
    ) AS j2
    ON m.id = j2.master
LEFT OUTER JOIN (
    SELECT fk_table.fk_master AS master,
           location.yuubin    AS yuubin3,
           fk_table.text      AS text3
    FROM fk_table
    LEFT OUTER JOIN location
    ON fk_table.fk_location = location.id
    WHERE fk_table.number = 3
    ) AS j3
    ON m.id = j3.master
ORDER BY m.name;

------------------------------
 Akira Taniguchi (DataSource) 
 akira ＠ datasource.jp
 http://www.datasource.jp