ごく一般的な掲示板アプリで thread とそのcomment数を表示するものです。
thread, comment の２つテーブルがあり
threadには id(PK), title
commentには id(PK), threadId, commentTime
のカラムがあります。
結果として
thread.id, thread.title, numOfComments, lastCommentTime
を取得したいのですが１時間かけてWebで探してもいまだ見つからないので、ここで質問させてもらいました。
numOfComments は thread に返信された comment 数、 last commentTime はそのthreadの最後のコメントの時間です。
尚、commentが一つもないthreadに関しては numOfComments が 0 で、lastCommentTime は null でも何でもいいです。
上記を満たすSQLをご教授ください。
(サブクエリ使ってください。)
また、もし可能であればこれが解決できるWebページを発見できるgoogle, yahooの検索ワード教えてください。
すごく初歩な質問なのに１時間かけても見つからないなんてどうかんがえてもおかしい・・・。
自分がおかしいのか、検索エンジンがまだまだ甘いのか・・・。
サブクエリ無しのMySQL 4.0 での説明とかいりません。 <- ヒットしました。
^^;
No.1
335
55
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です。
No.2
45328
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.検索エンジンで探しても代表的な使用例が出てくるだけで、具体的にぴったり一致するケースはないと思います。
No.3
1314393
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年以内のコメントだけを対象にして集計するような場合、
13
13
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;
のように使用することになると思います(上記の例ではサブクエリを使用しなくてもかけてしまいますが・・・)。
No.4
45328
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 ;
Mook
2007/06/25 13:46:29
2
1件しか返ってきません