複雑な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
となってしまいます。

回答No.5

yambejp · ベストアンサー

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

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

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

回答No.4

mitoneko

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

　＃２で掲載したＳＱＬは、実際にＭｙＳＱＬ５．５．８でテストテーブルを作成して動くのを確認したＳＱＬをそのままコピーした物です。
　ちなみに、＃３も構文を読んでいる限り、そのままの形で実行できるはずです。

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

お礼

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

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

補足

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

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)

回答No.3

yambejp

＞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

補足

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

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

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

回答No.2

mitoneko

複雑な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

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

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

回答No.1

yambejp

仕様が変じゃない？

>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

補足

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

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