--> 测试数据:#a if object_id('tempdb.dbo.#a') is not null drop table #a create table #a(id int, title varchar(8)) insert into #a select 1001, '标题1' union all select 1002, '标题2' --> 测试数据:#b if object_id('tempdb.dbo.#b') is not null drop table #b create table #b(id int, num varchar(8)) insert into #b select 1001, '01' union all select 1001, '02' union all select 1002, '05' --> 测试数据:#c if object_id('tempdb.dbo.#c') is not null drop table #c create table #c(num varchar(8), name varchar(8)) insert into #c select '01', '名称1' union all select '02', '名称2'select a.title, a.id, c.name from #a a inner join #b b on a.id=b.id left join #c c on b.num=c.num/* title id name -------- ----------- -------- 标题1 1001 名称1 标题1 1001 名称2 标题2 1002 NULL */
create table a(id int,title varchar(10)) insert into a values('1001', '标题1') insert into a values('1002', '标题2') create table b(id int,num varchar(10)) insert into b values(1001, '01') insert into b values(1001, '02') insert into b values(1002, '05') create table c(num varchar(10), name varchar(10)) insert into c values('01', '名称1') insert into c values('02', '名称2') goselect a.title , a.id , c.num from a left join b on a.id = b.id left join c on b.num = c.numdrop table a , b, c/* title id num ---------- ----------- ---------- 标题1 1001 01 标题1 1001 02 标题2 1002 NULL(所影响的行数为 3 行) */
所有的连接都可以一起使用 查询时按照从左往右[/code]
--> 测试数据: #A表 if object_id('tempdb.dbo.#A表') is not null drop table #A表 create table #A表 (id int,title varchar(5)) insert into #A表 select 1001,'标题1' union all select 1002,'标题2' --> 测试数据: #B表 if object_id('tempdb.dbo.#B表') is not null drop table #B表 create table #B表 (id int,num varchar(2)) insert into #B表 select 1001,'01' union all select 1001,'02' union all select 1002,'05' --> 测试数据: #C表 if object_id('tempdb.dbo.#C表') is not null drop table #C表 create table #C表 (num varchar(2),name varchar(5)) insert into #C表 select '01','名称1' union all select '02','名称2'select a.title,a.id, (case when exists(select * from #c表 c where c.num=b.num ) then b.num else null end) from #A表 a join #B表 b on a.id=b.id /* title id ----- ----------- ---- 标题1 1001 01 标题1 1001 02 标题2 1002 NULL(所影响的行数为 3 行)/*
select a.title,a.id, (case when exists(select * from #c表 c where c.num=b.num ) then b.num else null end)
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(id int, title varchar(8))
insert into #a
select 1001, '标题1' union all
select 1002, '标题2'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(id int, num varchar(8))
insert into #b
select 1001, '01' union all
select 1001, '02' union all
select 1002, '05'
--> 测试数据:#c
if object_id('tempdb.dbo.#c') is not null drop table #c
create table #c(num varchar(8), name varchar(8))
insert into #c
select '01', '名称1' union all
select '02', '名称2'select a.title, a.id, c.name from #a a inner join #b b on a.id=b.id left join #c c on b.num=c.num/*
title id name
-------- ----------- --------
标题1 1001 名称1
标题1 1001 名称2
标题2 1002 NULL
*/
insert into a values('1001', '标题1')
insert into a values('1002', '标题2')
create table b(id int,num varchar(10))
insert into b values(1001, '01')
insert into b values(1001, '02')
insert into b values(1002, '05')
create table c(num varchar(10), name varchar(10))
insert into c values('01', '名称1')
insert into c values('02', '名称2')
goselect a.title , a.id , c.num
from a left join b on a.id = b.id
left join c on b.num = c.numdrop table a , b, c/*
title id num
---------- ----------- ----------
标题1 1001 01
标题1 1001 02
标题2 1002 NULL(所影响的行数为 3 行)
*/
所有的连接都可以一起使用
查询时按照从左往右[/code]
if object_id('tempdb.dbo.#A表') is not null drop table #A表
create table #A表 (id int,title varchar(5))
insert into #A表
select 1001,'标题1' union all
select 1002,'标题2'
--> 测试数据: #B表
if object_id('tempdb.dbo.#B表') is not null drop table #B表
create table #B表 (id int,num varchar(2))
insert into #B表
select 1001,'01' union all
select 1001,'02' union all
select 1002,'05'
--> 测试数据: #C表
if object_id('tempdb.dbo.#C表') is not null drop table #C表
create table #C表 (num varchar(2),name varchar(5))
insert into #C表
select '01','名称1' union all
select '02','名称2'select
a.title,a.id,
(case when exists(select * from #c表 c where c.num=b.num ) then b.num else null end) from #A表 a join #B表 b on a.id=b.id
/*
title id
----- ----------- ----
标题1 1001 01
标题1 1001 02
标题2 1002 NULL(所影响的行数为 3 行)/*
a.title,a.id,
(case when exists(select * from #c表 c where c.num=b.num ) then b.num else null end)