关于表关联的问题:有一个寝室数据表如下,其中字段分别为:
寝室编码:分为3位,第一位代表学校,第二位代表区域最多9个,编码规则是,第三位代表具体的寝室号。即第一位编码为第二位的上级编码。--------------------------------------------------------TABLE1
寝室编码 上级编码 名称描述
113 11 法语系寝室
124 12 英语系寝室
135 13 日语系寝室
126 12 法语系寝室
12 1 B区
11 1 A区
1 root 学校
--------------------------------------------------------我有如下数据表:
--------------------------------------------------------TABLE2
姓名 寝室编码 上级编码
张三 113 11
李四 126 12
--------------------------------------------------------------------------------------------------------------------
问题如下:两个表关联要得到如下结果:姓名 寝室名 区域
张三 法语系寝室 A区
李四 法语系寝室 B区
---------------------------------------------------
就是同时关联那个编码表的时候卡住了,求助大家!
寝室编码:分为3位,第一位代表学校,第二位代表区域最多9个,编码规则是,第三位代表具体的寝室号。即第一位编码为第二位的上级编码。--------------------------------------------------------TABLE1
寝室编码 上级编码 名称描述
113 11 法语系寝室
124 12 英语系寝室
135 13 日语系寝室
126 12 法语系寝室
12 1 B区
11 1 A区
1 root 学校
--------------------------------------------------------我有如下数据表:
--------------------------------------------------------TABLE2
姓名 寝室编码 上级编码
张三 113 11
李四 126 12
--------------------------------------------------------------------------------------------------------------------
问题如下:两个表关联要得到如下结果:姓名 寝室名 区域
张三 法语系寝室 A区
李四 法语系寝室 B区
---------------------------------------------------
就是同时关联那个编码表的时候卡住了,求助大家!
from table2 a inner join table1 b on a.寝室编码=b.寝室编码
inner join table1 c on a.上级编码=b.寝室编码
insert into table1 select '113', '11','法语系寝室'
insert into table1 select '124', '12','英语系寝室'
insert into table1 select '135', '13','日语系寝室'
insert into table1 select '126', '12','法语系寝室'
insert into table1 select '12', '1','B区'
insert into table1 select '11', '1','A区'
insert into table1 select '1', 'root','学校'
create table TABLE2(姓名 nvarchar(10),寝室编码 varchar(10),上级编码 varchar(10))
insert into table2 select '张三','113', '11'
insert into table2 select '李四','126', '12'
go
select a.姓名,b.名称描述 as 寝室名,c.名称描述 as 区域
from table2 a inner join table1 b on a.寝室编码=b.寝室编码
inner join table1 c on a.上级编码=c.寝室编码
/*
姓名 寝室名 区域
---------- ---------- ----------
张三 法语系寝室 A区
李四 法语系寝室 B区(2 行受影响)
*/
go
drop table table1,table2
go
--> -->
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([寝室编码] int,[上级编码] nvarchar(10),[名称描述] nvarchar(5))
Insert #T1
select 113,N'11',N'法语系寝室' union all
select 124,N'12',N'英语系寝室' union all
select 135,N'13',N'日语系寝室' union all
select 126,N'12',N'法语系寝室' union all
select 12,N'1',N'B区' union all
select 11,N'1',N'A区' union all
select 1,N'root',N'学校'
Goif not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([姓名] nvarchar(2),[寝室编码] int,[上级编码] int)
Insert #T2
select N'张三',113,11 union all
select N'李四',126,12
Go
;WITH b
AS
(
SELECT a.*,[名称描述2]=a.[名称描述] FROM #T1 AS a ,#T1 AS b WHERE a.[上级编码]=RTRIM(b.[寝室编码]) AND b.[上级编码]='root'
UNION ALL
SELECT a.*,b.名称描述2 FROM #T1 AS a INNER JOIN b ON RTRIM(b.[寝室编码])=a.上级编码
)
Select
a.[姓名],b.[名称描述],b.名称描述2
from #T2 AS a
INNER JOIN b ON a.[寝室编码]=b.[寝室编码]/*
姓名 名称描述 名称描述2
张三 法语系寝室 A区
李四 法语系寝室 B区
*/
---位數都確定也可用select
a.姓名,
b.名称描述 as 寝室名,
c.名称描述 as 区域
from #T2 a,#T1 b,#T1 c
WHERE a.寝室编码=b.寝室编码 AND a.寝室编码 LIKE c.寝室编码+'_'
create table TABLE1(寝室编码 varchar(10),上级编码 varchar(10),名称描述 nvarchar(10))
insert into table1 select '113', '11','法语系寝室'
insert into table1 select '124', '12','英语系寝室'
insert into table1 select '135', '13','日语系寝室'
insert into table1 select '126', '12','法语系寝室'
insert into table1 select '12', '1','B区'
insert into table1 select '11', '1','A区'
insert into table1 select '1', 'root','学校'
create table TABLE2(姓名 varchar(10),寝室编码 varchar(10),上级编码 varchar(10))
insert into table2 select '张三','113', '11'
insert into table2 select '李四','126', '12'
go
select m.姓名 , n1.名称描述 寝室名 , n2.名称描述 区域
from table2 m
left join table1 n1 on m.寝室编码 = n1.寝室编码
left join table1 n2 on m.上级编码 = n2.寝室编码drop table table1 , table2/*
姓名 寝室名 区域
---------- ---------- ----------
张三 法语系寝室 A区
李四 法语系寝室 B区(所影响的行数为 2 行)
*/