表の結合と並べ替え

質問

次のようなテーブル構成で、

CREATE TABLE tbl1 (
id int PRIMARY KEY AUTO_INCREMENT
) ENGINE=InnoDB;
CREATE TABLE tbl2 (
id int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32)
) ENGINE=InnoDB;
CREATE TABLE tbl1_tbl2 (
id int PRIMARY KEY AUTO_INCREMENT,
tbl1_id int,
tbl2_id int,
FOREIGN KEY(tbl1_id) REFERENCES tbl1(id),
FOREIGN KEY(tbl2_id) REFERENCES tbl2(id)
) ENGINE=InnoDB;
CREATE TABLE tbl3 (
id INT PRIMARY KEY AUTO_INCREMENT,
tbl1_id int,
created datetime,
FOREIGN KEY(tbl1_id) REFERENCES tbl1(id)
) ENGINE=InnoDB;

[tbl1]
id
1
2
3

[tbl2]
id　　name
1　　　a
2　　　b

[tbl1_tbl2]
id　　tbl1_id　　tbl2_id
1　　　1　　　　　　1
2　　　1　　　　　　2
3　　　1　　　　　　2
4　　　2　　　　　　1
5　　　3　　　　　　1
6　　　3　　　　　　2

[tbl3]
id　　tbl1_id　　created
1　　　1　　　　2011-10-17 01:51:39
2　　　1　　　　2011-10-17 02:51:39
3　　　3　　　　2000-10-17 01:51:39

ここから次のような「tbl1のid」「tbl2の個数」「tbl3の最新のcreated」を基準とした
（下の例は「tbl1のid」を昇順にした結果です）
それぞれの並べ替た結果を得たいのですが、どのようなSQL文を記述すれば良いのでしょうか？

[tbl1のid]　[tbl2のnameのリスト]　[tbl3の最新のcreated]
　　1　　　　　　　　　a,b,b　　　　　　2011-10-17 02:51:39
　　2　　　　　　　　　a　　　　　　　　
　　3　　　　　　　　　a,b　　　　　　　2000-10-17 01:51:39

回答No.1

yambejp · ベストアンサー

select t1.id as t1id
,sub.t2name
,max(t3.created) as t3_max_created
from tbl1 as t1
left join (
select t1_2.tbl1_id as t1id
,group_concat(t2.name) as t2name
,count(*) as t2count
from tbl1_tbl2 as t1_2
inner join tbl2 as t2 on t1_2.tbl2_id=t2.id
group by t1id
) as sub on sub.t1id=t1.id
left join tbl3 as t3 on t3.tbl1_id=t1.id
group by t1id

として

(1)tbl1のid
order by t1id asc

(2)tbl2の個数
order by t2count asc

(3)tbl3の最新のcreated
order by t3_max_created asc

それぞれascをdescにすれば逆順です

お礼

ご回答ありがとうございます。
早速試してみて、希望通りの結果が得られました。
ありがとうございます。

申し訳ないのですが、もう一つ疑問が思い浮かんで
新たに次のようなtblテーブルを追加して

CREATE TABLE tbl (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32)
) ENGINE=InnoDB;

そしてtbl3に次のように

CREATE TABLE tbl3 (
id INT PRIMARY KEY AUTO_INCREMENT,
tbl1_id int,
tbl_id int,
created datetime,
FOREIGN KEY(tbl1_id) REFERENCES tbl1(id),
FOREIGN KEY(tbl_id) REFERENCES tbl(id)
) ENGINE=InnoDB;

tbl_idフィールドを追加して、次のような「tblのnameが例えば[aaa]のtbl3の最新のcreated」
を基準とした並べ替えの結果も追加したいのですが、これをするにはどのようなSQL文を記述すれば良いのでしょうか？

[tbl]
id　　name
1　　　aaa
2　　　bbb

[tbl3]
id　　tbl1_id　　created　　　　　　　　　　tbl_id
1　　　1　　　　2011-10-17 01:51:39　　　1
2　　　1　　　　2011-10-17 02:51:39　　　2
3　　　3　　　　2000-10-17 01:51:39　　　1

【得たい結果】
[tbl1のid]　[tbl2のnameのリスト]　[tbl3の最新のcreated]　　[tblのnameが[aaa]のtbl3の最新のcreated]
　　1　　　　　　　　　a,b,b　　　　　　2011-10-17 02:51:39　　　　2011-10-17 01:51:39
　　2　　　　　　　　　a　　　　　　　　
　　3　　　　　　　　　a,b　　　　　　　2000-10-17 01:51:39　　　　2000-10-17 01:51:39

回答No.2

yambejp

なんかつぎはぎになって申し訳ないですがたぶんこんな感じ
もう少し効率化できそうな気もしますが、一応動きます

select t1.id as t1id
,sub1.t2name
,sub1.t2count
,max(t3.created) as t3_max_created
,sub2.t3_aaa_created
from tbl1 as t1
left join (
select t1_2.tbl1_id as t1id
,group_concat(t2.name) as t2name
,count(*) as t2count
from tbl1_tbl2 as t1_2
inner join tbl2 as t2 on t1_2.tbl2_id=t2.id
group by t1id
) as sub1 on sub1.t1id=t1.id
left join tbl3 as t3 on t3.tbl1_id=t1.id
left join (
select
tbl1_id,max(created) as t3_aaa_created
from tbl3
inner join tbl on tbl_id=tbl.id
and name='aaa'
group by tbl1_id
) as sub2 on sub2.tbl1_id=t1.id
group by t1id
order by t1id

お礼

ご回答ありがとうございます。
これも希望通りの結果を得ることができました。
ありがとうございます。