自连接
自连接是一种特殊的表连接,它是指相互连接的表在物理上同为一张表,但是逻辑上是多张表。自连接通常用于表中的数据有层次结构,如区域表、菜单表、商品分类表等。
自连接语法
select A.column,B.column
from table A,table B
where A.column = B.column;
创建表、插入数据
create table area(
id int not null auto_increment primary key comment '区域id',
pid int not null comment '符父id(0-省份)',
name varchar(30) comment '区域名称'
)default charset=utf8;
insert into area(id,pid,name) values(1,0,'湖北省');
insert into area(id,pid,name) values(2,1,'荆州');
insert into area(id,pid,name) values(3,1,'武汉');
insert into area(id,pid,name) values(4,0,'江苏省');
insert into area(id,pid,name) values(5,4,'南京');
insert into area(id,pid,name) values(6,4,'苏州');
示例:
select * from area where pid<>0;
select A.id,A.name,B.name as provincename
from area A,area B
where A.pid = B.id and A.pid<>0;