T1
c1 c2
'a1' 'b1'
'a2' 'b2'
'a3' 'b3'T2
CC1 CC2 cc3
'a1' 'd1' null
'a2' 'd2' null
'a2' 'd3' 'e1'
'a3' 'd4' 'e2'
'a3' 'd5' 'e3'
'a3' 'd6' 'e4'查询出
C1 C2 CC2
'a1' 'b1' 'd1'
'a2' 'b2' 'd2'
'a3' 'b3' NULL
c1 c2
'a1' 'b1'
'a2' 'b2'
'a3' 'b3'T2
CC1 CC2 cc3
'a1' 'd1' null
'a2' 'd2' null
'a2' 'd3' 'e1'
'a3' 'd4' 'e2'
'a3' 'd5' 'e3'
'a3' 'd6' 'e4'查询出
C1 C2 CC2
'a1' 'b1' 'd1'
'a2' 'b2' 'd2'
'a3' 'b3' NULL
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('T1') is not null
drop table T1
go
create table T1 (c1 varchar(10) ,c2 varchar(10))
insert T1 select
'a1', 'b1' union select
'a2', 'b2' union select
'a3', 'b3'
go
if OBJECT_ID('T2') is not null
drop table T2
go
create table T2 (cc1 varchar(10) ,cc2 varchar(10),cc3 varchar(10))
insert T2 select
'a1', 'd1' ,null union select
'a2', 'd2', null union select
'a2', 'd3', 'e1' union select
'a3', 'd4', 'e2' union select
'a3', 'd5', 'e3' union select
'a3', 'd6', 'e4'
go
select
t1.c1,c2,cc2=max(case when cc3 IS null then cc2 end )
from t1 join t2 on t1.c1=t2.cc1
group by t1.c1,c2
/*
c1 c2 cc2
---------- ---------- ----------
a1 b1 d1
a2 b2 d2
a3 b3 NULL*/
T1
c1 c2
'a1' 'b1'
'a2' 'b2'
'a3' 'b3'
'a4' 'b4'
'a5' 'b5'
T2
CC1 CC2 cc3
'a1' 'd1' null
'a2' 'd2' null
'a2' 'd3' 'e1'
'a3' 'd4' 'e2'
'a3' 'd5' 'e3'
'a3' 'd6' 'e4'麻烦再帮帮忙
C1 C2 CC2
'a1' 'b1' 'd1'
'a2' 'b2' 'd2'
'a3' 'b3' NULL
'a4' 'b4' NULL
'a5' 'b5' NULL
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('T1') is not null
drop table T1
go
create table T1 (c1 varchar(10) ,c2 varchar(10))
insert T1 select
'a1', 'b1' union select
'a2', 'b2' union select
'a3', 'b3' union select
'a4' ,'b4' union select
'a5' ,'b5'go
if OBJECT_ID('T2') is not null
drop table T2
go
create table T2 (cc1 varchar(10) ,cc2 varchar(10),cc3 varchar(10))
insert T2 select
'a1', 'd1' ,null union select
'a2', 'd2', null union select
'a2', 'd3', 'e1' union select
'a3', 'd4', 'e2' union select
'a3', 'd5', 'e3' union select
'a3', 'd6', 'e4'go
select
t1.c1,c2,cc2=max(case when cc3 IS null then cc2 end )
from t1 left join t2 on t1.c1=t2.cc1
group by t1.c1,c2
/*
c1 c2 cc2
---------- ---------- ----------
a1 b1 d1
a2 b2 d2
a3 b3 NULL
a4 b4 NULL
a5 b5 NULL*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-19 21:01:38
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([c1] varchar(2),[c2] varchar(2))
insert [t1]
select 'a1','b1' union all
select 'a2','b2' union all
select 'a3','b3'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([CC1] varchar(2),[CC2] varchar(2),[cc3] varchar(2))
insert [T2]
select 'a1','d1',null union all
select 'a2','d2',null union all
select 'a2','d2','e1' union all
select 'a3','d3','e2' union all
select 'a3','d3','e3' union all
select 'a3','d3','e4'
--------------开始查询--------------------------
select
t1.c1,c2,cc2=max(case when cc3 is null then cc2 else null end)
from
t1 join t2 t
on
t1.c1=t.cc1
group by
t1.c1,c2
----------------结果----------------------------
/*c1 c2 cc2
---- ---- ----
a1 b1 d1
a2 b2 d2
a3 b3 NULL
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
*/