34,590
社区成员
发帖
与我相关
我的任务
分享
--两次LEFT JOIN
SELECT a.NAME, b1.NAME, b2.name
FROM A
LEFT JOIN B b1
ON a.部门 = b1.id
LEFT JOIN B b2
ON a.职位 = b2.id
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-14 15:40:10
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(4),[部门] int,[职位] int)
insert [A]
select 1,'张三',1,2
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[name] varchar(8),[parentid] int)
insert [B]
select 1,'产品部',0 union all
select 2,'产品研发',1
--------------开始查询--------------------------
--------------开始查询--------------------------
select a.Id,a.NAME,b.NAME AS [部门],c.NAME AS [职位]
from [A] INNER JOIN b ON a.部门=b.id INNER JOIN b AS c ON a.职位=c.id
----------------结果----------------------------
/*
Id NAME 部门 职位
----------- ---- -------- --------
1 张三 产品部 产品研发
*/