mysql一个表的省市县级联_mySQL 实现省市县联动查询

drop database if exists test;

create database test charset=utf8;

use test;

create table t(

id int,

code int,

name varchar(20),

parentcode int

);

insert into t values (1,10000,'四川',0);

insert into t values (2,20000,'安徽',0);

insert into t values (3,11000,'成都',10000);

insert into t values (4,12000,'绵阳',10000);

insert into t values (5,21000,'合肥',20000);

insert into t values (6,22000,'宣城',20000);

insert into t values (7,11100,'浦江',11000);

insert into t values (8,11300,'温江',11000);

insert into t values (9,11300,'郫县',11000);

select t3.name1,t3.name2,t4.name from

(select t1.code code1,t1.name name1,t1.parentcode parentcode1,t2.code code2,t2.name name2,t2.parentcode parentcode2

from t t1 left join t t2 on t1.code=t2.parentcode where t1.parentcode=0) t3 left join t t4 on t3.code2=t4.parentcode;