在线等，关联查询问题，比如省市关联查询，一级省，二级市

Question

province表，和city表
想输出这种形式：array(
                                        "0" =>array(
                                                 "province"=>"山东“
                                                 "belong"=>array(
                                                          array("city"=>"济南”),
                                                          array("city"=>"青岛”),
                                                         );
                              "1" =>array(
                                                 "province"=>"江苏“
                                                 "belong"=>array(
                                                          array("city"=>"苏州”),
                                                          array("city"=>"南京”),
                                                         );
                                                   );
                                                   );
想要这种形式

Answer 1

库里就是按省和市放的，province.id = city.pid，省的id就是市的pid，就这两个表，编码是utf8

Answer 2

create temporary table province (id int, name varchar(10)) charset gbk;
create temporary table city (id int, pid int, name varchar(10)) charset gbk;insert into province values (1, '山东');
insert into city values (1, 1, '济南');
insert into city values (2, 1, '青岛');
insert into province values (2, '江苏');
insert into city values (3, 2, '苏州');
insert into city values (4, 2, '南京');select id, 0 as pid, name from province
union
select pid, id, name from city
order by 1,2
id pid name 
1  0   山东 
1  1   济南 
1  2   青岛 
2  0   江苏 
2  3   苏州 
2  4   南京