学习笔记（1):MySQL数据库从入门到实战课-自连接查询的场景及使用

立即学习:https://edu.csdn.net/course/play/27328/370701?utm_source=blogtoedu

create table area(
    id int not null auto_increment primary key comment '区域id',
    pid int not null comment '父id(0-省份)',
    name varchar(30) comment '区域名称'
);

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 A.id, A.name, B.name as provinceName
    from area A, area B
    where A.pid = B.id and A.pid<>0;