求tsql,通过id连接三表t1,t2,t3成一新表,新表中最date1和date2的最大值,没有对应行的取值为nullt1
id xm
1 tom
2 jack
3 Harryt2
id date1
1 2008-1-1
1 2009-1-1
2 2006-1-1t3
id date2
3 2004-1-1
3 2007-1-1id xm date1 date2
1 tom 2009-1-1 null
2 jack 2006-1-1 null
3 Harry null 2007-1-1
id xm
1 tom
2 jack
3 Harryt2
id date1
1 2008-1-1
1 2009-1-1
2 2006-1-1t3
id date2
3 2004-1-1
3 2007-1-1id xm date1 date2
1 tom 2009-1-1 null
2 jack 2006-1-1 null
3 Harry null 2007-1-1
into newtb
from t1 a
left join (select id,max(date1) date1 from t2 group by id) b
on a.id=b.id
left join (select id,max(date2) date2 from t3 group by id) c
on a.id=c.id
insert t1 select 1 , 'tom'
insert t1 select 2 , 'jack'
insert t1 select 3 , 'Harry' create table t2 (id int, date1 varchar(10))
insert t2 select 1 , '2008-1-1'
insert t2 select 1 , '2009-1-1'
insert t2 select 2 , '2006-1-1' create table t3 (id int, date2 varchar(10))
insert t3 select 3 , '2004-1-1'
insert t3 select 3 , '2007-1-1' select a.*,b.date1,c.date2
into newtb
from t1 a
left join (select id,max(date1) date1 from t2 group by id) b
on a.id=b.id
left join (select id,max(date2) date2 from t3 group by id) c
on a.id=c.idselect * from newtb
/*
id xm date1 date2
----------- ---------- ---------- ----------
1 tom 2009-1-1 NULL
2 jack 2006-1-1 NULL
3 Harry NULL 2007-1-1(3 行受影响)
*/
drop table t1,t2,t3,newtb
借用楼上兄弟的数据
select t1.id,t1.xm, max(t2.date1)as date1,max(t3.date2) as date2 from t1
left join t2 on t1.id=t2.id
left join t3 on t1.id=t3.id
group by t1.id,t1.xm
order by t1.id
--------------------------
1 tom 2009-1-1 NULL
2 jack 2006-1-1 NULL
3 Harry NULL 2007-1-1
Go
Set Nocount On
if not object_id('t1') is null
drop table t1
Go
Create table t1([id] int,[xm] nvarchar(50))
Insert t1
select 1,N'tom' union all
select 2,N'jack' union all
select 3,N'Harry'
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([id] int,[date1] Datetime)
Insert t2
select 1,'2008-1-1' union all
select 1,'2009-1-1' union all
select 2,'2006-1-1'
Go
if not object_id('t3') is null
drop table t3
Go
Create table t3([id] int,[date2] Datetime)
Insert t3
select 3,'2004-1-1' union all
select 3,'2007-1-1'
/*id xm date1 date2
1 tom 2009-1-1 null
2 jack 2006-1-1 null
3 Harry null 2007-1-1*/--1
Select a.id,a.xm,b.date1,c.date2
From t1 As a
Left Outer Join(Select id,Max(date1) As date1 From t2 Group By id) As b On b.id=a.id
Left Outer Join(Select id,Max(date2) As date2 From t3 Group By id) As c On c.id=a.id
--2
Select a.id,a.xm,b.date1,c.date2
From t1 As a
Outer Apply(Select Max(date1) As date1 From t2 Where id=a.id) As b
Outer Apply(Select Max(date2) As date2 From t3 Where id=a.id) As c