表一:
Aid Atype Btype
1 1 3
2 2 1
3 4 2 表二:
id ABname
1 主板
2 CPU
3 内存
4 显卡 二表关联后,输出效果:
Aid Aname Bname
1 主板 内存
2 CPU 主板
3 显卡 CPU
Aid Atype Btype
1 1 3
2 2 1
3 4 2 表二:
id ABname
1 主板
2 CPU
3 内存
4 显卡 二表关联后,输出效果:
Aid Aname Bname
1 主板 内存
2 CPU 主板
3 显卡 CPU
from tb1 as a join tb2 as b on a.atype=b.id
join tb2 as c on a.btype=c.id
INSERT INTO test1
SELECT 1 , 1 , 3
UNION ALL
SELECT 2 , 2 , 1
UNION ALL
SELECT 3 , 4 , 2
create TABLE test2(id int ,abname varchar(20))
INSERT INTO test2
SELECT 1 , '主板'
UNION ALL
SELECT 2 , 'CPU'
UNION ALL
SELECT 3 , '内存'
UNION ALL
SELECT 4 , '显卡'SELECT aid,MAX(aname) aname,MAX(bname) bname
FROM (
SELECT aid,b.abname AS aname ,NULL AS bname
FROM test1 a INNER JOIN test2 b ON A.atype=b.id
UNION
SELECT aid,NULL AS aname , b.abname AS bname
FROM test1 a INNER JOIN test2 b ON A.btype=b.id
)a
GROUP BY aid/*
aid aname bname
----------- -------------------- --------------------
1 主板 内存
2 CPU 主板
3 显卡 CPU
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)*/
insert tb1 select 1,1,3 union select 2,2,1 union select 3,4,2create table tb2 (id int,abname nvarchar(32))
insert tb2 select 1,N'主板 ' UNION SELECT 2,N'CPU' UNION SELECT 3,N'内存' UNION SELECT 4,N'显卡'go
select a.aid,b.abname as aname,c.abname as bname
from tb1 as a join tb2 as b on a.atype=b.id
join tb2 as c on a.btype=c.id
ORDER BY A.AID
/*
aid aname bname
1 主板 内存
2 CPU 主板
3 显卡 CPU
*/drop table tb1
drop table tb2
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-09-19 11:16:07
-- Version:
-- Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[表一]
if object_id('[表一]') is not null drop table [表一]
go
create table [表一]([Aid] int,[Atype] int,[Btype] int)
insert [表一]
select 1,1,3 union all
select 2,2,1 union all
select 3,4,2
--> 测试数据:[表二]
if object_id('[表二]') is not null drop table [表二]
go
create table [表二]([id] int,[ABname] varchar(4))
insert [表二]
select 1,'主板' union all
select 2,'CPU' union all
select 3,'内存' union all
select 4,'显卡'
goselect
a.Aid,
b.ABname as Aname,
c.ABname as Aname
from
[表一] a
inner join
[表二] b
on
a.Atype=b.id
inner join
[表二] c
on
a.Btype=c.id/*
Aid Aname Aname
----------- ----- -----
2 CPU 主板
3 显卡 CPU
1 主板 内存(3 行受影响)
*/
use TEST
goif OBJECT_ID('dbo.tab1') is not null
drop table dbo.tabl
gocreate table tab1(
aid int ,
atype int ,
btype int
)insert into tab1
select 1,1,3 union all
select 2,2,1 union all
select 3,4,2if OBJECT_ID('dbo.tab2' ) is not null
drop table tab2
gocreate table tab2 (
id int,
Abname varchar(10)
)insert into tab2
select 1 , '主板' union all
select 2 , 'CPU' union all
select 3 , '内存' union all
select 4 , '显卡'select t.aid , (select abname from tab2 as a where t.atype = a.id ) aname ,
(select abname from tab2 as b where t.btype = b.id ) aname
from dbo.tab1 as t /*
aid aname aname
1 主板 内存
2 CPU 主板
3 显卡 CPU
*/