SQLの質問です。 (MySQL 5.0)

ごく一般的な掲示板アプリで thread とそのcomment数を表示するものです。
thread, comment の2つテーブルがあり
threadには id(PK), title
commentには id(PK), threadId, commentTime
のカラムがあります。

結果として
thread.id, thread.title, numOfComments, lastCommentTime
を取得したいのですが1時間かけてWebで探してもいまだ見つからないので、ここで質問させてもらいました。
numOfComments は thread に返信された comment 数、 last commentTime はそのthreadの最後のコメントの時間です。
尚、commentが一つもないthreadに関しては numOfComments が 0 で、lastCommentTime は null でも何でもいいです。

上記を満たすSQLをご教授ください。
(サブクエリ使ってください。)

また、もし可能であればこれが解決できるWebページを発見できるgoogle, yahooの検索ワード教えてください。
すごく初歩な質問なのに1時間かけても見つからないなんてどうかんがえてもおかしい・・・。
自分がおかしいのか、検索エンジンがまだまだ甘いのか・・・。
サブクエリ無しのMySQL 4.0 での説明とかいりません。 <- ヒットしました。
^^;

回答の条件
  • 1人3回まで
  • 登録:2007/06/25 11:44:32
  • 終了:2007/07/02 11:45:03

回答(4件)

id:freemann No.1

freemann回答回数307ベストアンサー獲得回数492007/06/25 12:27:24

ポイント23pt

SELECT thread.id,thread.title,A.lastCommentTime,A.numOfComments

FROM thread LEFT JOIN (SELECT COUNT(*) as numOfComments,MAX(commentTime) as lastCommentTime,threadID FROM comment GROUP BY threadID) AS A

ON thread.id = A.threadID

----------------------------------------------------

コメントのないスレッドのnumOfCommentsとlastCommentTimeはnullです。

id:Yota No.2

Yota回答回数453ベストアンサー獲得回数282007/06/25 12:54:44

ポイント23pt

SELECT

thread.id, thread.title, sub.numOfComments, sub.lastCommentTime

FROM thread

INNER JOIN

(SELECT threadId,COUNT(id) AS numOfComments,MAX(commentTime) AS lastCommentTime

FROM comment GROUP BY threadId)

AS sub

ON thread.id = sub.threadId ;

1.こんなところでどうでしょうか。

2.検索エンジンで探しても代表的な使用例が出てくるだけで、具体的にぴったり一致するケースはないと思います。

id:Mook No.3

Mook回答回数1312ベストアンサー獲得回数3912007/06/25 13:29:40

ポイント22pt

今回のケースはサブクエリを使用するケースではないと思います。


サブクエリはある特定の条件を満たすものを、選択するのに使用することが多く、

今回のように全体を対象とする場合は必要ではありません。


ご希望の結果を出すクエリ文は

SELECT  t.id, t.title, count(threadid) AS numOfComments, max( commentTime ) as lastCommentTime
FROM thread t LEFT JOIN comment c on t.id = c.threadid
GROUP BY c.threadid ORDER BY t.id;

のようになると思います。


サブクエリを使う例としては、1年以内のコメントだけを対象にして集計するような場合、

SELECT  t.id, t.title, count(threadid) AS numOfComments, max( commentTime ) as lastCommentTime
FROM thread t LEFT JOIN comment c on t.id = c.threadid
AND c.id IN ( SELECT id FROM comment WHERE DATEDIFF( NOW(), commentTime ) <= 356 )
GROUP BY c.threadid ORDER BY t.id;

のように使用することになると思います(上記の例ではサブクエリを使用しなくてもかけてしまいますが・・・)。


MySQL サブクエリ構文

id:pooh2005

1件しか返ってきません

2007/06/26 23:49:20
id:Yota No.4

Yota回答回数453ベストアンサー獲得回数282007/06/25 14:52:24

ポイント22pt

回答者2です。これを考慮してなかったので訂正させてください。

尚、commentが一つもないthreadに関しては numOfComments が 0 で、lastCommentTime は null でも何でもいいです。

SELECT

thread.id, thread.title,

COALESCE(sub.numOfComments,0) AS numOfComments, sub.lastCommentTime

FROM thread

LEFT OUTER JOIN

(SELECT threadId,COUNT(id) AS numOfComments,MAX(commentTime)

AS lastCommentTime

FROM comment GROUP BY threadId)

AS sub

ON thread.id = sub.threadId ;

  • id:Mook
    うーん、サブクエリを中間テーブルとして使用する方法ならこういった使い方も「あり」のようですね。

    あまりこういった使い方をしないので、不適切な回答になってしまったようです。

    失礼しました。

  • id:Mook
    一応こちらで動作確認はしているのですが、一件しか出ないとは後者を実行した場合でしょうか?

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

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

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

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