精选SQL

1：父子关系的级联查询

Mysql中实现：

建表：

CREATE TABLE t(id INT PRIMARY KEY,name NVARCHAR(10),parentId INT)；

插入数据：
INSERT INTO t
SELECT 1,'北京市',0
UNION ALL SELECT 2,'山东省',0
UNION ALL SELECT 3,'昌平区',1
UNION ALL SELECT 4,'海淀区',1
UNION ALL SELECT 5,'沙闸镇',3
UNION ALL SELECT 6,'马池口镇',3
UNION ALL SELECT 7,'中关村',4
UNION ALL SELECT 8,'上地',4
UNION ALL SELECT 9,'烟台市',2
UNION ALL SELECT 10,'青岛市',2
UNION ALL SELECT 11,'五通桥区',9
UNION ALL SELECT 12,'马边区',9
UNION ALL SELECT 13,'定文镇',10
UNION ALL SELECT 14,'罗成镇',10
查询：

select t.name as '一级地区' ,saddr.name as '二级地区',taddr.name as '三级地区' from t 
join (SELECT id,name,parentId from t) saddr on t.id=saddr.parentId
join (SELECT id,name,parentId from t) taddr on saddr.id=taddr.parentId

 

 

 

参考：https://blog.youkuaiyun.com/qq_41568597/article/details/84309503