27,579
社区成员
发帖
与我相关
我的任务
分享
select id, sortid1, b.name as name1, sortid2, c.name as name1, info from ta a left join tb b on a.sortid1=b.sortid left join tb c on a.sortid2=c.sortid
SELECT ID,RORTID1,B.NAME AS NAME1 , sortid2,C.NAME AS NAME2,INFO
FROM A LEFT JOIN B ON A.RORTID1=B.RORTID
LEFT JOIN B C ON A.RORTID2=B.RORTID
/*
[Author]: OrchidCat[OC]_轻骑兵(向高手学习...)
[Time]: 2010-07-13 18:19:58
[Place]: From Beijing
[Version]:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
*/
--> 测试数据:[A1]
if object_id('[A1]') is not null drop table [A1]
create table [A1]([id] int,[sortid1] int,[sortid2] int,[info] varchar(5))
insert [A1]
select 1,1,3,'info' union all
select 2,2,4,'info2'
--> 测试数据:[B2]
if object_id('[B2]') is not null drop table [B2]
create table [B2]([sortid] int,[name] varchar(5))
insert [B2]
select 1,'分类1' union all
select 2,'分类2' union all
select 3,'分类3' union all
select 4,'分类4'
select * from [A1]
select * from [B2]
SELECT A1.id,sortid1,B2.NAME,A1.sortid2,B.NAME,A1.[Info]
FROM dbo.A1
LEFT JOIN B2 ON A1.sortid1 = B2.sortid
LEFT JOIN B2 B ON A1.sortid2 = B.sortid
/*
id sortid1 NAME sortid2 NAME Info
----------- ----------- ----- ----------- ----- -----
1 1 分类1 3 分类3 info
2 2 分类2 4 分类4 info2
(2 行受影响)
*/