最新レコードを抽出し外部結合する方法について

質問

お世話になります。

現在、最新レコードを抽出し外部結合するSQLを考えているのですが、実現できておりません。
実現できるSQLをご存知の方、いらっしゃいましたら情報を頂けますでしょうか。
# 私の使用しているのは、PostgreSQL8.3となります。

実現したい内容は、以下となります。
　１．テーブルＡから「名前」でグループ化して最新の「更新日付」のレコードを抽出。
　２．１の結果とテーブルＢを「名前」で結合。
　３．テーブルＡの「名前」、「点数」、「更新日付」とテーブルＢの「判定」を抽出。
　　　⇒ただしテーブルＡに情報がある場合は、テーブルＢの判定結果を「０」にして出力。

　　　　　テーブルＡ　　　　　　　　　　　　　　　テーブルＢ
----------------------------　　　-----------------------
　名前｜　点数｜　更新日付　　　　　　　名前　｜　判定　
----------------------------　　　-----------------------
ＡＡＡ　　　９８　　　2011/4/1　　　　　　　　ＡＡＡ　　　０　
ＡＡＡ　　　６０　　　2011/4/3　　　　　　　　ＢＢＢ　　　１　
ＢＢＢ　　　７０　　　2011/4/2　　　　　　　　ＣＣＣ　　　１　
ＢＢＢ　　　３５　　　2011/4/4　　　　　　　　ＤＤＤ　　　１　
ＤＤＤ　　　９８　　　2011/4/1　　　　　　　　ＥＥＥ　　　０　
ＥＥＥ　　　４７　　　2011/4/5　　　　　　　　 ＦＦＦ　　　０　
ＧＧＧ　　　８０　　　2011/4/6　　　　　　　　ＧＧＧ　　　１　

【出力結果】
---------------------------------------------
　名前　｜　点数　　｜　更新日付　｜　判定　
---------------------------------------------
ＡＡＡ　　　　６０　　　　　2011/4/3　　　　 　０　
ＢＢＢ　　　　３５　　　　　2011/4/4　　　　　０（１⇒０に変更）　
ＣＣＣ　　　NULL　　　　　NULL　　　　　　　１　
ＤＤＤ　　　　９８　　　　　2011/4/1　　　　　０（１⇒０に変更）　
ＥＥＥ　　　　４７　　　　　2011/4/5　　　　 　０　
ＦＦＦ　　　　NULL　　　　　NULL　　　　　 　０　
ＧＧＧ　　　　８０　　　　　2011/4/6　　　　　０（１⇒０に変更）

お手数お掛け致しますが、ご教示のほどよろしくお願い致します。

回答No.1

gacky-79 · ベストアンサー

PostgreSQL 8.3 ですと、「最新の「更新日付」のレコードを抽出」の箇所でセルフジョインが必要になります。

具体的なSQLはこのような感じです。最新の日付を max() で計算しているので、更新日付のデータ型を date にするか、文字列の場合はゼロ埋め ("2011/04/03") しておいてください。

CREATE TABLE テーブルA (名前 text, 点数 integer, 更新日付 date);
INSERT INTO テーブルA VALUES
('AAA', 98, '2011/4/1'),
('AAA', 60, '2011/4/3'),
('BBB', 70, '2011/4/2'),
('BBB', 35, '2011/4/4'),
('DDD', 98, '2011/4/1'),
('EEE', 47, '2011/4/5'),
('GGG', 80, '2011/4/6');

CREATE TABLE テーブルB (名前 text, 判定 integer);
INSERT INTO テーブルB VALUES
('AAA', 0),
('BBB', 1),
('CCC', 1),
('BBB', 1),
('DDD', 0),
('EEE', 0),
('GGG', 1);

SELECT
名前, 点数, 更新日付,
CASE WHEN max_a.名前 IS NOT NULL THEN 0 ELSE 判定 END AS 判定
FROM
(SELECT 名前, max(更新日付) AS 更新日付 FROM テーブルA GROUP BY 名前) AS max_a
JOIN
テーブルA USING (名前, 更新日付)
RIGHT JOIN テーブルB USING (名前);

お礼

お礼が遅くなり申し訳ありません。

ご回答ありがとうございます。
頂いたSQLで実現できました。また、更新日付のデータ型についても了解致しました。

ご協力ありがとうございました。

回答No.2

yamada_g

最新レコードを抽出するのにnot existsを使う方法もありますね。
そこの部分以外はgacky-79さんのSQLをそのまま使わせていただきました。

SELECT
名前, 点数, 更新日付,
CASE WHEN max_a.名前 IS NOT NULL THEN 0 ELSE 判定 END AS 判定
FROM
(SELECT * FROM テーブルA AS A1
WHERE NOT EXISTS(
SELECT * FROM テーブルA AS A2
WHERE A1.名前 = A2.名前 AND A1.更新日付 < A2.更新日付
)) AS テーブルA
RIGHT JOIN テーブルB USING (名前);

お礼

お礼が遅くなり申し訳ありません。

ご回答ありがとうございます。
not existsを使用しても実現可能なんですね。性能面も考えて
NO.1の方のとどちらを使用するか検討させて頂きます。

ご協力ありがとうございました。