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

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

解決済みの質問

表の結合と並べ替え

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

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

投稿日時 - 2011-10-17 12:41:43

QNo.7077103

すぐに回答ほしいです

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

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にすれば逆順です

投稿日時 - 2011-10-17 14:18:39

お礼

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

申し訳ないのですが、もう一つ疑問が思い浮かんで
新たに次のような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

投稿日時 - 2011-10-17 16:06:27

ANo.1

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

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

回答(2)

ANo.2

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

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

投稿日時 - 2011-10-17 16:43:54

お礼

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

投稿日時 - 2011-10-17 18:30:01