SQLの条件抽出について

質問

ちょっと躓いてしまったのでアドバイスが頂けたらと思います。

user_accountというテーブルと、logというテーブルがあります。
logというテーブルには各ユーザーの書き込み履歴が残されており、日付がtimestamp形式で格納されています。
ユーザー一覧に最終発言内容をくっ付けて表示したいのですがうまくいきません。

SELECT DISTINCT * FROM user_accounts LEFT JOIN (SELECT * FROM log ORDER BY update_date DESC ) aaa ON user_accounts.pkey = aaa.user_accounts_pkey;

などと記述してみましたが、思うような結果になりません。
自分の理想とする結果は以下のような出力結果です。
+----+----+----+--+-----------------+----------------+-----------+
|　pkey|　user|　pass|　　　　　　comment|　update_date|
+----+----+----+--+-----------------+----------------+-----------+
|　　1　|　test1|　pass01|test03 written by test1|　40813|
|　　2　|　test2|　pass02|testestest　　　　　　　　|　40690|

どうか、お知恵を貸してください。

回答No.1

nora1962 · ベストアンサー

WINDOW関数が使えるなら

select pkey, user, pass, comment, update_date
from ( SELECT user_accounts.*, log.comment, log.update_date,
row_number() over(partition by user_account.pkey order by log.update_date ) rn
FROM user_accounts LEFT JOIN log
ON user_accounts.user = log.user_accounts_pkey) T
where rn = 1;

そうでなければ

SELECT user_accounts.*, log.comment, log.update_date
FROM user_accounts LEFT JOIN
( select * from log
where not exists
( select 1 from log l2
where log.user_accounts_pkey=l2.user_accounts_pkey
and log.update_date<l2.update_date ) ) l
ON user_accounts.pkey = l.user_accounts_pkey

お礼

いち早く反応してくださり、ありがとうございます。
not existsにこのような使い方があるとは。。。
もっと精進します。ありがとうございます。

回答No.3

umekihajime

訂正します。

まずは素直に最終発言を抽出します。
select user_account_pkey,max(update_data) from log group by user_account_pkey

これを使って log から必要なレコードをのみを抽出、user_account テーブルと結合して結果を得ます。
select ua.pkey,ua.user,ua.pass,l.comment,l.update_date
from log l,user_account ua
where l.user_account_pley = ua.pkey
and (l.user_account_pkey,update_data) in
(
select user_account_pkey,max(update_data) from log group by user_account_pkey
)
※SQLは机上で書きました動作確認はしていません。

お礼

ありがとうございます、group byの使い方が分からず、四苦八苦していた所にumekihajime様の書き込みがあり、大変参考になりました。

自分が求めていた書き方に一番近い書き方をされていたものの、動作確認をしていないとの事もあり、umekihajime様には大変恐縮ではありますが、最初に書き込みを下さった方をベストアンサーとさせていただきました。

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

回答No.2

umekihajime

まずは素直に最優発言を抽出します。
select user_account_pkey,max(update_data) from log group by user_account_pkey

これを使って log から必要なレコードをのみを抽出、user_account テーブルと結合して結果を得ます。
select ua.pkey,ua.user,ua.pass,l.comment,l.update_date
from log l,user_account ua
where l.user_account_pley = ua.pkey
and (l.user_account_pkey,update_data) in
(
select user_account_pkey,max(update_data) from log group by user_account_pkey
)
※SQLは机上で書きました動作確認はしていません。