表 a
id caption
01 张三
02 李四
03 王五
表 b
id a_id b_id c_id
1 01 02 03
做这样一个连接查询,结果把表b 中的编号专成人名 1 张三 李四 王五
id caption
01 张三
02 李四
03 王五
表 b
id a_id b_id c_id
1 01 02 03
做这样一个连接查询,结果把表b 中的编号专成人名 1 张三 李四 王五
(select caption from a where id=a_id) as a_name,
(select caption from a where id=b_id) as b_name,
(select caption from a where id=c_id) as c_name
from b
A.id,
B.caption,
C.caption,
D.caption
FROM tb2 AS A
LEFT JOIN tb1 AS B
ON A.a_id=B.id
LEFT JOIN tb1 AS C
ON A.b_id=C.id
LEFT JOIN tb1 AS D
ON A.c_id=D.id
SELECT B.ID,A.CAPTION,B.CAPTION,C.CAPTION
FROM TB T INNER JOIN TA A ON A.ID=T.A_ID
INNER JOIN TA B ON B.ID=T.B_ID
INNER JOIN TA C ON C.ID=T.C_ID
declare @a table(id varchar(10),caption varchar(10))
insert into @a
select '01','张三' union all
select '02','李四' union all
select '03','王五' declare @b table(id int,a_id varchar(10),b_id varchar(10),c_id varchar(10))
insert into @b
select 1,'01','02','03'
select id,(select caption from @a where id=a_id),
(select caption from @a where id=b_id),(select caption from @a where id=c_id) from @b
/*
id
----------- ---------- ---------- ----------
1 张三 李四 王五(1 行受影响)
*/
--2
select b.id,
a1.caption as a_name,
a2.caption as b_name,
a3.caption as c_name
from b left join a as a1 on b.id=a1.id
left join a as a2 on b.id=a2.id
left join a as a3 on b.id=a3.id
insert into @a
select '01',N'张三' union all
select '02',N'李四' union all
select '03',N'王五'
declare @b table(id int,a_id varchar(10),b_id varchar(10),c_id varchar(10))
insert into @b
select 1,'01','02','03'
SELECT T.ID,A.CAPTION,B.CAPTION,C.CAPTION
FROM @B T INNER JOIN @A A ON A.ID=T.A_ID
INNER JOIN @A B ON B.ID=T.B_ID
INNER JOIN @A C ON C.ID=T.C_ID
/*ID CAPTION CAPTION CAPTION
----------- ---------- ---------- ----------
1 张三 李四 王五(1 個資料列受到影響)*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-17 09:13:27
----------------------------------------------------------------
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([id] varchar(2),[caption] varchar(4))
insert [a1]
select '01','张三' union all
select '02','李四' union all
select '03','王五'
--------------开始查询--------------------------select id,
max(case caption when '张三' then ''end) as 张三,
max(case caption when '李四' then ''end) as 李四,
max(case caption when '王五' then ''end) as 王五
from [a1]
group by id
----------------结果----------------------------
/*
*/
DROP TABLE A
GO
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE A(ID VARCHAR(10),CAPTION VARCHAR(20))
INSERT INTO A (ID,CAPTION)
SELECT '01','张三' union all
SELECT '02','李四' union all
SELECT '03','王五'
go
create table B(ID int ,a_id varchar(10),b_id varchar(10),c_id varchar(10))
INSERT INTO B(ID,A_ID,B_ID,C_ID)
SELECT 1,'01','02','03'
GO
--查询
SELECT B.ID ,
Max(CASE WHEN B.A_ID = A.ID THEN A.CAPTION END) AS A_ID,
max(CASE WHEN B.b_ID = A.ID THEN A.CAPTION END) AS b_ID,
max(CASE WHEN B.C_ID = A.ID THEN A.CAPTION END) AS c_ID FROM B LEFT JOIN A
ON B.A_ID = A.ID OR B.B_ID = A.ID OR B.C_ID = A.ID
GROUP BY B.ID /*
ID A_ID b_ID c_ID
----------- -------------------- -------------------- --------------------
1 张三 李四 王五
(1 行受影响)
*/
declare @a table(id varchar(10),caption varchar(10))
insert into @a
select '01','张三' union all
select '02','李四' union all
select '03','王五' declare @b table(id int,a_id varchar(10),b_id varchar(10),c_id varchar(10))
insert into @b
select 1,'01','02','03';with cte as
(select id,
(select caption from tb1 where id=a_id) as a_caption,
(select caption from tb1 where id=b_id) as b_caption,
(select caption from tb1 where id=c_id) as c_captione
from tb2 )
select * from cte