需求:有一张行政区划表,想只保留中国区域的,中国之外的全部清除
1、表结构
2、查询出所有【中国区域】的行政区划(递归查询)
WITH RECURSIVE r AS ( SELECT * FROM tbl_nation_area WHERE id = '185' union ALL SELECT na.* FROM tbl_nation_area na, r WHERE na.pid = r.id ) SELECT id FROM r ORDER BY id
3、删除除【中国区域】以外的其它地区
DELETE FROM cm_nation_area WHERE id not in(WITH RECURSIVE r AS ( SELECT * FROM cm_nation_area WHERE id = '185' union ALL SELECT cna.* FROM cm_nation_area cna, r WHERE cna.pid = r.id ) SELECT id FROM r ORDER BY id);