人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQLの質問です。
掲示板の新着情報を取り出すSQL文をどのように書いたらよいか困っています。

掲示板に書き込みをすると、messagesというテーブルに、id、parent_id、created、title、bodyという列の行が追加されます。
idには自動で数字が入ります。
parent_idには、その書き込みが返信の時には返信先のidが入り、返信でない時には自身のidと同じ数字が入ります。ただし、返信の書き込みには返信できないようにアプリケーション側で制限しています。
createdには、自動で日付が入ります。
titleには、返信の時には返信先のtitleの値が入り、返信でない時にはタイトルとして書きこまれた文字列が入ります。
bodyは返信かそうでないかに関係なく、本文として書きこまれた文字列が入ります。

このテーブルから新着の書き込み20件を取り出したいのですが、取り出すのは、あくまで返信ではない書き込みだけにしたいと考えています。そのために、返信がある書き込みは、返信が付いた日をcreatedの日付と見なして取り出したいのですが、どのようにSQL文を書いたらよいのか見当がつきません。ご教授いただけると助かります。

RDBMSはMySQLです。
よろしくお願いします。

●質問者: ishimarum
●カテゴリ:インターネット ウェブ制作
✍キーワード:BODY MySQL RDBMS SQL アプリケーション
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● Mook
●27ポイント

取り出すフィールドはとりあえず全てにしていますが、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を表示)。

いただいた回答ですと、『返信ではない書き込み』は取り出せるのですが、『返信が付いて新着となった古い書き込み』を取り出せないかと思います(最後に返信がついた日付も)。

質問が言葉足らずで申し訳ありません。

改めてご検討いただけると大変うれしいです。

よろしくお願いいたします。


2 ● うぃんど
●27ポイント ベストアンサー

例1

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

例2

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
◎質問者からの返答

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

()で括った文の結果を()の外の文に使用できるんですね。

大変勉強になりました。


3 ● chuken_kenkou
●26ポイント

実機確認していませんが、こんな感じのSQLで実装できるのではないかと思います。

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

なお、質問する場合は、次のような情報を提示するようにしてください。

  1. MySQLのバージョン(MySQL 4.1、5.0、5.1といったレベルまで最低限)
  2. 母体データ例(対象行、対象でない行を含む)
  3. 得たい結果例
◎質問者からの返答

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

COALESCE句というものを初めて知りました。

また、質問の仕方の不備もご指摘ありがとうございました。

大変勉強になりました。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ