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

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 での説明とかいりません。 <- ヒットしました。
^^;

●質問者: pooh2005
●カテゴリ:コンピュータ インターネット
✍キーワード:Google MySQL PK SQL Web
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● freemann
●23ポイント

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です。


2 ● Yota
●23ポイント

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


3 ● Mook
●22ポイント

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


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

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


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

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 サブクエリ構文

◎質問者からの返答

1件しか返ってきません


4 ● Yota
●22ポイント

回答者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 ;

関連質問


●質問をもっと探す●



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