从新开个贴问一下这个怎么查询

码拜

8年 ago

create table  a
(
id   varchar(10),
reid varchar(20),
topid varchar(20)
)
insert into a values( “63651”,”63476″,”63476″)
insert into a values( “63652”,”63477″,”63477″)
insert into a values( “63653”,”63481″,”63477″)
insert into a values( “63654”,”63481″,”63477″)
insert into a values( “63655”,”63481″,”63477″)
insert into a values( “63656”,”63482″,”63477″)
insert into a values( “63657”,”63482″,”63477″)
create table b
(
id   varchar(10),
typeid  varchar(20),
pic   varchar(200)
)
insert into b values( “1”,”63652″,”/uploads”)
insert into b values( “2”,”63653″,”/uploads”)
insert into b values( “3”,”63654″,”/uploads”)
insert into b values( “4”,”63655″,”/uploads”)
insert into b values( “5”,”63656″,”/uploads”)
insert into b values( “6”,”63657″,”/uploads”)
create table c
(
id   varchar(10),
aid  varchar(20),
url   varchar(200)
)
insert into c values( “1”,”1″,”/iamges1″)
insert into c values( “2”,”1″,”/iamges1″)
insert into c values( “3”,”1″,”/iamges1″)
insert into c values( “4”,”2″,”/iamges2″)
insert into c values( “5”,”2″,”/iamges2″)
insert into c values( “6”,”2″,”/iamges2″)
insert into c values( “7”,”3″,”/iamges3″)
insert into c values( “8”,”3″,”/iamges3″)
insert into c values( “9”,”3″,”/iamges3″)
insert into c values( “10”,”4″,”/iamges4″)
insert into c values( “11”,”4″,”/iamges4″)
insert into c values( “12”,”4″,”/iamges4″)
insert into c values( “13”,”5″,”/iamges5″)
insert into c values( “14”,”5″,”/iamges5″)
insert into c values( “15”,”5″,”/iamges5″)
insert into c values( “16”,”6″,”/iamges6″)
insert into c values( “17”,”6″,”/iamges6″)
insert into c values( “18”,”6″,”/iamges6″)
表a的id关联表b的typeid
表b的id关联表c的aid
给定一个表a的topid的值，例如63477。
本人想要得到这样的结果，一行数据有一个主题图三个小图,按照版主说的提问格式改了一下，请教：
id          pic                                         url
1    /uploads              /images1  /images1  /images1
2    /uploads              /images2 /images2  /images2
3    /uploads              /images3  /images3  /images3
4    /uploads              /images4  /images4  /images4
5    /uploads              /images5  /images5  /images5
6    /uploads              /images6  /images6  /images6
*/

解决方案

SELECT
b.id,
b.pic,
GROUP_CONCAT(c.url)
FROM
b
LEFT JOIN c ON b.id=c.aid
GROUP BY b.id

mysql> select id,pic,
    ->  (select group_concat(url SEPARATOR " ") from c where aid=b.id) as url
    -> from b
    -> where typeid in (select id from a where topid=63477);
+--+--+--+
| id   | pic      | url                        |
+--+--+--+
| 1    | /uploads | /iamges1 /iamges1 /iamges1 |
| 2    | /uploads | /iamges2 /iamges2 /iamges2 |
| 3    | /uploads | /iamges3 /iamges3 /iamges3 |
| 4    | /uploads | /iamges4 /iamges4 /iamges4 |
| 5    | /uploads | /iamges5 /iamges5 /iamges5 |
| 6    | /uploads | /iamges6 /iamges6 /iamges6 |
+--+--+--+
6 rows in set (0.00 sec)
mysql>