sql中关于连接查询的问题

[Mic_Gary]

组织不清楚，干脆用个例子
给出两个表

员工表Employee，申请表Application，Apl_User和Apl_Checker都是对应表Employee中的Emp_Id，如何用sql语句查询相对应的名字。即最后的结果应该是这样的

[开着拖拉机泡妞]

select a.Api_id,e.emp_name,b.emp_name from Apl_User a inner join Employee e on a.Api_user=e.emp_id inner join Employee b on a.Api_checker=b.emp_id

[發糞塗牆]

select a.apl_id,e.emp_name,c.emp_name from Application a left join Employee e on a.apl_user=e.emp_id left join employee c on a.apl_checker=e.emp_id

[Mic_Gary]

引用 2 楼 DBA_Huangzj 的回复:

select a.apl_id,e.emp_name,c.emp_name
from Application a left join Employee e on a.apl_user=e.emp_id
left join employee c on a.apl_checker=e.emp_id

大侠，您的结果是这样的
少了一列

[Mic_Gary]

引用 1 楼 TravyLee 的回复:

select a.Api_id,e.emp_name,b.emp_name from Apl_User a inner join Employee e on a.Api_user=e.emp_id inner join Employee b on a.Api_checker=b.emp_id

查出来空的

[發糞塗牆]

select a.apl_id,c.emp_name from Application a left join employee c on a.apl_checker=e.emp_id 单独这样执行呢？是不是你的数据有问题

[發糞塗牆]

引用 4 楼 Mic_Gary 的回复:

[quote=引用 1 楼 TravyLee 的回复:] select a.Api_id,e.emp_name,b.emp_name from Apl_User a inner join Employee e on a.Api_user=e.emp_id inner join Employee b on a.Api_checker=b.emp_id

查出来空的[/quote]left join都美数据的话inner join当然为空

[發糞塗牆]

写错了个别名，不好意思

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-26 11:12:33
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[Employee]
if object_id('[Employee]') is not null drop table [Employee]
go 
create table [Employee]([Emp_id] int,[Emp_name] varchar(4))
insert [Employee]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王武'
--> 测试数据：[Application]
if object_id('[Application]') is not null drop table [Application]
go 
create table [Application]([apl_id] int,[apl_user] int,[apl_checker] int)
insert [Application]
select 10001,1,3 union all
select 10002,2,3
--------------开始查询--------------------------

select a.apl_id,e.emp_name,c.emp_name
from [Application] a left join Employee e on a.apl_user=e.emp_id
left join employee c on a.apl_checker=c.emp_id
----------------结果----------------------------
/* 
(2 row(s) affected)
apl_id      emp_name emp_name
----------- -------- --------
10001       张三       王武
10002       李四       王武
*/

[Mic_Gary]

引用 7 楼 DBA_Huangzj 的回复:

写错了个别名，不好意思

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-26 11:12:33
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[Employee]
if object_id('[Employee]') is not null drop table [Employee]
go 
create table [Employee]([Emp_id] int,[Emp_name] varchar(4))
insert [Employee]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王武'
--> 测试数据：[Application]
if object_id('[Application]') is not null drop table [Application]
go 
create table [Application]([apl_id] int,[apl_user] int,[apl_checker] int)
insert [Application]
select 10001,1,3 union all
select 10002,2,3
--------------开始查询--------------------------

select a.apl_id,e.emp_name,c.emp_name
from [Application] a left join Employee e on a.apl_user=e.emp_id
left join employee c on a.apl_checker=c.emp_id
----------------结果----------------------------
/* 
(2 row(s) affected)
apl_id      emp_name emp_name
----------- -------- --------
10001       张三       王武
10002       李四       王武
*/

牛逼哄哄的大神，膜拜啊膜拜

[Mic_Gary]

引用 7 楼 DBA_Huangzj 的回复:

大侠，我有个问题想要请教，不知你能不能帮我看一下 http://bbs.csdn.net/topics/390674042