if object_id('tb') is not null
drop table tb
go
create table tb(id int,name varchar(50),parentid int)
insert into tb select 1,'安徽省',0
insert into tb select 2,'湖南省',0
insert into tb select 3,'合肥市',1
insert into tb select 4,'巢湖市',1
insert into tb select 5,'肥西县',3
insert into tb select 6,'肥东县',3
insert into tb select 7,'肥东乡',6
insert into tb select 8,'肥东村',7
insert into tb select 9,'湖南乡',2
insert into tb select 10,'湖南镇',9
gowith dom
as
(select * from tb where parentid=3
union all
select a.* from tb a inner join dom b on a.parentid=b.id)select * from dom
drop table tb
go
create table tb(id int,name varchar(50),parentid int)
insert into tb select 1,'安徽省',0
insert into tb select 2,'湖南省',0
insert into tb select 3,'合肥市',1
insert into tb select 4,'巢湖市',1
insert into tb select 5,'肥西县',3
insert into tb select 6,'肥东县',3
insert into tb select 7,'肥东乡',6
insert into tb select 8,'肥东村',7
insert into tb select 9,'湖南乡',2
insert into tb select 10,'湖南镇',9
gowith dom
as
(select * from tb where parentid=3
union all
select a.* from tb a inner join dom b on a.parentid=b.id)select * from dom
例如:
id,name,pid,其中id是PK,name代表内容,pid代表父id
比如用这个模式来写中国的行政划分:
1,中国,null
2,山东,1
3,北京,1
4,浙江,1
5,济南,2
6,历下区,5
7,杭州,4
……
这样,用递归遍历一下就OK了。
我在执行with dom时,老是提示“)附近有语法错误”
请问这是怎么回事,谢谢。