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