[转]地区表的应用

--创建province表

create table province
(
id varchar(5) not null,
name varchar(10),
lastid varchar(5)
)
go

--插入数据
insert into province values('100','河北','')
insert into province values('200','北京','')
insert into province values('300','衡水','100')
insert into province values('400','朝阳','200')
insert into province values('500','安徽','')
insert into province values('600','马鞍山','500')

--取数
select a.[id],
[name]=a.[name]+isnull('('+(select name from province where id=a.lastid)+')','')
from province a

--所显示的效果

id      name

100 河北
200 北京
300 衡水(河北)
400 朝阳(北京)
500 安徽
600 马鞍山(安徽)