掲示板の新着情報を取り出すSQL文をどのように書いたらよいか困っています。
掲示板に書き込みをすると、messagesというテーブルに、id、parent_id、created、title、bodyという列の行が追加されます。
idには自動で数字が入ります。
parent_idには、その書き込みが返信の時には返信先のidが入り、返信でない時には自身のidと同じ数字が入ります。ただし、返信の書き込みには返信できないようにアプリケーション側で制限しています。
createdには、自動で日付が入ります。
titleには、返信の時には返信先のtitleの値が入り、返信でない時にはタイトルとして書きこまれた文字列が入ります。
bodyは返信かそうでないかに関係なく、本文として書きこまれた文字列が入ります。
このテーブルから新着の書き込み20件を取り出したいのですが、取り出すのは、あくまで返信ではない書き込みだけにしたいと考えています。そのために、返信がある書き込みは、返信が付いた日をcreatedの日付と見なして取り出したいのですが、どのようにSQL文を書いたらよいのか見当がつきません。ご教授いただけると助かります。
RDBMSはMySQLです。
よろしくお願いします。
No.2
2625
1149
27pt
例１
SELECT m1.id, m1.parent_id, m1.title, m1.body, (SELECT MAX(m2.created) FROM messages m2 WHERE m2.id = m1.parent_id) AS created FROM messages m1 WHERE m1.parent_id = m1.id ORDER BY m1.created DESC LIMIT 0,20
例２
SELECT m1.id, m1.parent_id, m1.title, m1.body, (SELECT m2.created FROM messages m2 WHERE m2.id = m1.parent_id ORDER BY m2.created DESC LIMIT 0,1) AS created FROM messages m1 WHERE m1.parent_id = m1.id ORDER BY m1.created DESC LIMIT 0,20
No.1
1314393
27pt
取り出すフィールドはとりあえず全てにしていますが、FROM 以降は下記のようでどうでしょうか。
SELECT * FROM messages WHERE parent_id = id ORDER BY created DESC LIMIT 0,20
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_10.htm
早速のご回答ありがとうございました。
新着として取り出した際に画面に日付を表示したいのですが、返信がある書き込みは最後に付いた返信の日付を更新日として表示したいのです（返信がない書き込みはその書き込みのcreatedを表示）。
いただいた回答ですと、『返信ではない書き込み』は取り出せるのですが、『返信が付いて新着となった古い書き込み』を取り出せないかと思います（最後に返信がついた日付も）。
質問が言葉足らずで申し訳ありません。
改めてご検討いただけると大変うれしいです。
よろしくお願いいたします。
No.2
26251149
ここでベストアンサー
27pt
例１
SELECT m1.id, m1.parent_id, m1.title, m1.body, (SELECT MAX(m2.created) FROM messages m2 WHERE m2.id = m1.parent_id) AS created FROM messages m1 WHERE m1.parent_id = m1.id ORDER BY m1.created DESC LIMIT 0,20
例２
SELECT m1.id, m1.parent_id, m1.title, m1.body, (SELECT m2.created FROM messages m2 WHERE m2.id = m1.parent_id ORDER BY m2.created DESC LIMIT 0,1) AS created FROM messages m1 WHERE m1.parent_id = m1.id ORDER BY m1.created DESC LIMIT 0,20
ご回答ありがとうございました。
（）で括った文の結果を（）の外の文に使用できるんですね。
大変勉強になりました。
No.3
72254
26pt
実機確認していませんが、こんな感じのSQLで実装できるのではないかと思います。
22
22
select
id
,parent_id
,coalesce(
(select
max(created)
from messages
where m.id=parent_id
)
,created) as created
,title
,body
from messages as m
where id=parent_id
order by created desc limit 0,20
なお、質問する場合は、次のような情報を提示するようにしてください。
ご回答ありがとうございました。
COALESCE句というものを初めて知りました。
また、質問の仕方の不備もご指摘ありがとうございました。
大変勉強になりました。
Mook
2011/05/02 22:38:32
うぃんど
2
2
1
ご回答ありがとうございました。
（）で括った文の結果を（）の外の文に使用できるんですね。
大変勉強になりました。