SQLの質問です。

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

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

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

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

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/05/02 21:58:54
  • 終了:2011/05/09 12:26:29

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/05/02 22:53:07

ポイント27pt

例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
id:ishimarum

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

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

大変勉強になりました。

2011/05/09 12:22:27

その他の回答(2件)

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912011/05/02 22:08:21

ポイント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

id:ishimarum

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

新着として取り出した際に画面に日付を表示したいのですが、返信がある書き込みは最後に付いた返信の日付を更新日として表示したいのです(返信がない書き込みはその書き込みのcreatedを表示)。

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

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

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

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

2011/05/02 22:21:44
id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/05/02 22:53:07ここでベストアンサー

ポイント27pt

例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
id:ishimarum

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

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

大変勉強になりました。

2011/05/09 12:22:27
id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542011/05/03 01:02:00

ポイント26pt

実機確認していませんが、こんな感じの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. 得たい結果例
id:ishimarum

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

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

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

大変勉強になりました。

2011/05/09 12:25:53
  • id:Mook
    ちょっと今検証はできないのですが、

    SELECT * FROM messages WHERE id IN
    ( SELECT distinct parent_id FROM messages WHERE ORDER BY created DESC LIMIT 0,20 )
    でどうでしょうか。
  • id:windofjuly
    うぃんど 2011/05/02 22:55:42
    訂正
    誤 m2.id = m1.parent_id
    正 m2.parent_id = m1.id
  • id:Mook
    元のメッセージに、返信の時刻を上書きしたかったんですね。
    私の回答ではダメそうです。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません