みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

複雑なorder by

例えば、テーブルが以下
<<T_BOARD(掲示板テーブル)>>
・id(スレッドID)
・parent_id(親スレッドID)
・created(作成日)

データが以下(親スレッドはparent_id = 0)
(id,parent_id,created)
1,0,2011/5/10
2,1,2011/4/11
3.0.2011/1/13
4,3,2011/5/20
5,0,2011/6/23
のようにあるとしたら、

スレッドを親スレッドごとに取得し、作成日の降順に取得したいです。
(id,parent_id,created)
5,0 (親スレッド自身が最新)
3,0(id2が2011/5/20)
1,0(id4が2011/4/11)

現在は
select buid_id,created from T_ROOM where parent_id = 0 order by created;
これだと、親スレッドの作成日降順にしかとれないので、
5,0 2011/6/23
1,0,2011/5/10
3.0.2011/1/13
となってしまいます。

投稿日時 - 2011-06-23 22:14:51

QNo.6830859

すぐに回答ほしいです

質問者が選んだベストアンサー

条件が以下3つ、すなわち
・子スレッドは必ず親スレッドより新しくなる
・親スレッドに対して子スレッドは複数存在する
・子スレッドに対して子スレッドはない

であれば、以下の単純なクエリで必要十分かと

SELECT IF(parent_id=0,id,parent_id) AS xid,MAX(created) AS created
FROM T_ROOM
GROUP BY xid
ORDER BY created DESC

汎用性を考えると以下になるかもしれません。

SELECT T1.id,T1.parent_id,COALESCE(MAX(T2.created),T1.created) AS created
FROM `T_ROOM` AS T1
LEFT JOIN `T_ROOM` AS T2
ON (T1.id=T2.parent_id)
WHERE T1.parent_id=0
GROUP BY T1.id
ORDER BY created DESC

投稿日時 - 2011-06-27 09:43:00

ANo.5

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

>実はUserテーブルがあり、user_idをLEFT JOINでさらにUserテーブルをつなげて自分なりにやってみたのですが、うまくいきませんでした…

 #2で掲載したSQLは、実際にMySQL5.5.8でテストテーブルを作成して動くのを確認したSQLをそのままコピーした物です。
 ちなみに、#3も構文を読んでいる限り、そのままの形で実行できるはずです。

 うまくいかなかったなら、どんなSQLを作成して、どう動かなかったのかをちゃんと説明していただかないと、これ以上のアドヴァイスのしようが無いわけです。(「うまくいかない」にも、いろいろあります。構文エラーが出て動かないから、思ったのと違う結果が出るとか、いつまで待っても結果が帰ってこない(要するに遅すぎる)まで多種多様です。)
 補足していただく時には、MySQLのバージョンも書いておいた方が良いですよ。MySQLはバージョンが変わると、とたんに出来ることと出来ないことが大幅に変わることがありますから。

投稿日時 - 2011-06-26 21:39:36

補足

失礼いたしました。
質問の範囲を超えてしまうと思いましたので…
そもそも、質問を省略してしまったのがいけなかったですね。

MySqlのヴァージョンはこれですか?コマンドプロンプトの貼り付けです。
Server version: 5.0.83-community-nt MySQL Community Edition (GPL)

Userテーブルを自分なりにつなげてみましたが、
順番がうまくとれませんでした。
取得したいフィールドはすべてになります。

select * from treeboards AS Treeboard LEFT JOIN users AS User
on (Treeboard.user_id = User.id)
where Treeboard.id IN(
SELECT Treeboard.id
FROM treeboards AS Treeboard
LEFT JOIN treeboards AS T2
ON (Treeboard.id=T2.parent_id)
OR (Treeboard.id=T2.id)
WHERE Treeboard.parent_id=0 and Treeboard.del_flg = 0
GROUP BY Treeboard.id
ORDER BY MAX(T2.created) DESC)

投稿日時 - 2011-06-28 12:39:33

お礼

すみません。
実際使っているテーブル名を書いてしまいました。(混乱させてしまいすみません)

treeboardsはT_ROOMと同じです。
今回追加取得したいテーブルはUsersです。

投稿日時 - 2011-06-28 12:51:10

ANo.3

>5,0 (親スレッド自身が最新)
>3,0(id4が2011/5/20)
>1,0(id2が2011/4/11)

createdに関しては子文書を優先でよいですね?
また子文書は複数存在しないということでよいですね?

SELECT T1.id,T1.parent_id,COALESCE(T2.created,T1.created) AS created
FROM `T_ROOM` AS T1
LEFT JOIN `T_ROOM` AS T2
ON (T1.id=T2.parent_id)
WHERE T1.parent_id=0
GROUP BY T1.id
ORDER BY created DESC

投稿日時 - 2011-06-24 18:15:51

補足

毎度説明不足ですみません。
子スレッドは必ず親スレッドより新しくなりますので、
必然的に子スレッドが優先になります。

親スレッドに対して子スレッドは複数存在します。
ただし、子スレッドに対して子スレッドはありません。

実はUserテーブルがあり、user_idをLEFT JOINでさらにUserテーブルをつなげて自分なりにやってみたのですが、うまくいきませんでした…

投稿日時 - 2011-06-24 20:43:48

ANo.2

 複雑なorder byと言うよりは、特殊な結合と言うタイトルの話題かな。下手をすると、複雑な特殊な結合になりそうな気がする。
 まず、書いてあるデータのパターンだけで動けば良いのなら・・・(細かい突っ込みを入れると、結果の例示、間違ってますけど・・・まぁ、正しいデータに脳内変換して・・・)

SELECT p.id, greatest(max(p.created), ifnull(max(c.created),'1800/01/01')) FROM t_room p
left outer join t_room c on p.id = c.parent_id
WHERE p.parent_id = 0
group by p.id
order by greatest(max(p.created), ifnull(max(c.created),'1800/01/01')) desc

 とかいう形になるでしょうか。
 自分との自己結合で、直系の子スレッドの日付を取ってきて、親スレッドのidでグループ化して、求める作成日をmax関数で取っているわけですが、子スレッドが一つも無い時に、createdがnullを返すと、greatestの結果もnullになってしまうので、ifnullで回避しています。

 問題は、このデータに、例えば、
  6,4,2011/10/10
 なんて一行が入ると、破綻します。つまり、子供の子供ですね。これを扱うには、再帰が必要になりますが・・・
 これをちゃんと扱うSQLはちょっと思いつけないです。

投稿日時 - 2011-06-23 23:33:57

ANo.1

仕様が変じゃない?

>5,0 (親スレッド自身が最新)

は、まぁいいとして

>3,0(id2が2011/5/20)

parentが3なのはid4ですよ??

>1,0(id4が2011/4/11)

parentが1なのはid2ですよ??
そしてid2のcreatedとid1のcreatedだとid1の方が最新ですよ??

一体全体どうしたいのでしょうか・・・
勝手に想像して、こうしてみましたが、命題が間違っているだけになんとも

SELECT T1.id,T1.parent_id,MAX(T2.created) AS created
FROM `T_ROOM` AS T1
LEFT JOIN `T_ROOM` AS T2
ON (T1.id=T2.parent_id)
OR (T1.id=T2.id)
WHERE T1.parent_id=0
GROUP BY T1.id
ORDER BY created DESC

投稿日時 - 2011-06-23 23:22:48

補足

ありがとうございます。
簡潔な例題を提示しようとして、修正を繰り返し、逆におかしなことになっていました。
ご指摘の通りの間違いでした。
問題の意図を理解していただいてよかったです。
書き直してみました。

5,0 (親スレッド自身が最新)
3,0(id4が2011/5/20)
1,0(id2が2011/4/11)

投稿日時 - 2011-06-24 12:58:56

あなたにオススメの質問