SQL Server 資料庫之自串連樣本

 

建立資料庫環境

use master
go
if not exists(select * from sysdatabases where name='stuDB')
create database stuDB
go

 

建立資料表

use stuDB
go
create table AreaTable
(
   Subid int primary key,
   AddName varchar(16),
   ParentId int null
)
go

 

往建立表中添加資料

添加資料

insert into AreaTable
select 86,'中國',null union
select 430731,'長沙市',43 union
select 43,'湖南省',86 union
select 43073101,'長沙縣',430731 union
select 43073102,'寧鄉縣',430731 union
select 430732,'嶽陽市',43 union
select 43073202,'平江縣',430732 union
select 43073103,'湘陰縣',430732 
go

select * from AreaTable

 

 

解決問題: 樹形階層顯示
/*
 這是一個地區表，裡面存放了地區名及其所屬上級地區，假設現在需要查詢出各地區及其所屬上級地區。
*/

自串連的方法1：

select AddName as '地區名',
  (select AddName from AreaTable as a
where a.Subid = b.ParentId ) as '上級地區名'
from AreaTable as b

 

自串連的方法2：

select a.AddName as '地區名',
       b.AddName as '上級地區名'
from AreaTable as a
left join AreaTable as b
on a.ParentId = b.Subid

 

自串連三級（左聯結）：

自串連三級（左聯結）

select a.AddName as '地區名',
       b.AddName as '上級名',
       c.AddName as '上上級名'
from AreaTable as a
left join AreaTable as b
on a.ParentId = b.Subid
left join AreaTable as c
on b.ParentId = c.Subid

 

自串連四級（內連結）：

自串連四級（內連結）

select a.AddName as '縣城',
       b.AddName as '市區',
       c.AddName as '省份',
       d.AddName as '國家'
from AreaTable as a
inner join AreaTable as b
on a.ParentId = b.Subid
inner join AreaTable as c
on b.ParentId = c.Subid
inner join AreaTable as d
on c.ParentId = d.Subid