tsql:三表 ta,tb,tc join,且只join tc中日期最大的那行
例如:
ta
id data1
1 1
2 2
tb
id data2
1 11
2 22
tc
id data3 riqi
1 111 2009-1-4
1 112 2009-1-11应查询出下表
1d data1 data2 data3 riqi
1 1 11 112 2009-1-11
2 2 22 null null
例如:
ta
id data1
1 1
2 2
tb
id data2
1 11
2 22
tc
id data3 riqi
1 111 2009-1-4
1 112 2009-1-11应查询出下表
1d data1 data2 data3 riqi
1 1 11 112 2009-1-11
2 2 22 null null
from ta,tb,(
select *
from tc tc1
where not exists (select 1 from tc tc2 where tc1.id=tc2.id and tc1.riqi<tc2.riqi)
)temp
where ta.id=tb.id and ta.id=temp.id
left join
(
Select ID, max(riqi) as riqi from tc group by ID
)c on b.ID = c.ID
where a.ID = b.ID
insert into ta values(1 , 1)
insert into ta values(2 , 2)
create table tb(id int, data2 int)
insert into tb values(1 , 11)
insert into tb values(2 , 22)
create table tc(id int, data3 int , riqi datetime)
insert into tc values(1 , 111 , '2009-1-4')
insert into tc values(1 , 112 , '2009-1-11')
go--第1种方法
select ta.* ,
tb.data2,
data3 = (select data3 from tc where riqi = (select max(tc.riqi) from tc where tc.id = ta.id)),
riqi = (select max(tc.riqi) from tc where tc.id = ta.id)
from ta inner join tb on ta.id = tb.id
/*
id data1 data2 data3 riqi
----------- ----------- ----------- ----------- ------------------------------------------------------
1 1 11 112 2009-01-11 00:00:00.000
2 2 22 NULL NULL(所影响的行数为 2 行)
*/--第2种方法
select ta.*,
tb.data2,
m.data3,
m.riqi
from ta inner join tb on ta.id = tb.id
left join (select t.* from tc t where riqi = (select max(riqi) from tc where id = t.id)) m
on ta.id = m.id
/*
id data1 data2 data3 riqi
----------- ----------- ----------- ----------- ------------------------------------------------------
1 1 11 112 2009-01-11 00:00:00.000
2 2 22 NULL NULL(所影响的行数为 2 行)
*/--第3种方法
select ta.*,
tb.data2,
m.data3,
m.riqi
from ta inner join tb on ta.id = tb.id
left join (select t.* from tc t where not exists (select 1 from tc where id = t.id and riqi > t.riqi)) m
on ta.id = m.id
/*
id data1 data2 data3 riqi
----------- ----------- ----------- ----------- ------------------------------------------------------
1 1 11 112 2009-01-11 00:00:00.000
2 2 22 NULL NULL(所影响的行数为 2 行)
*/drop table ta , tb, tc
if object_id('ta') is not null drop table ta
create table ta(id int ,data1 int )
insert ta values(1,1)
insert ta values(2,2)if object_id('tb') is not null drop table tb
create table tb (id int,date2 int )
insert tb values(1,11)
insert tb values(2,22)if object_id('tc') is not null drop table tc
create table tc (id int,data3 int,riqi datetime)
insert tc values(1,11,2009-1-4)
insert tc values(1,112,2009-1-11)select * from ta a left join tb b on a.id = b.id left join tc c on a.id = c.id
where riqi = (select max(riqi) from tc where id = a.id) or riqi is nullid data1 id date2 id data3 riqi
----------- ----------- ----------- ----------- ----------- ----------- -----------------------
1 1 1 11 1 11 1905-06-28 00:00:00.000
2 2 2 22 NULL NULL NULL(2 row(s) affected)
select ta.*,
tb.data2,
m.data3,
m.riqi
from ta inner join tb on ta.id = tb.id
left join (select t.* from tc t where not exists (select 1 from tc where id = t.id and riqi > t.riqi)) m
on ta.id = m.id
declare @ta table (id int,data1 int)
insert into @ta select 1,1
union all select 2,2
declare @tb table (id int,data2 int)
insert into @tb select 1,11
union all select 2,22
declare @tc table (id int,data3 int,riqi datetime)
insert into @tc select 1,111,'2009-1-4'
union all select 1,112,'2009-1-11'
select a.id,a.data1,b.data2,c.data3,c.riqi from @ta a join @tb b
on a.id=b.id left join
(select * from @tc a where not exists
(select 1 from @tc where id=a.id and riqi>a.riqi)) c
on a.id= c.idid data1 data2 data3 riqi
----------- ----------- ----------- ----------- -----------------------
1 1 11 112 2009-01-11 00:00:00.000
2 2 22 NULL NULL(2 行受影响)
if object_id('ta') is not null drop table ta
create table ta(id int ,data1 int )
insert ta values(1,1)
insert ta values(2,2)if object_id('tb') is not null drop table tb
create table tb (id int,date2 int )
insert tb values(1,11)
insert tb values(2,22)if object_id('tc') is not null drop table tc
create table tc (id int,data3 int,riqi datetime)
insert tc values(1,11,'2009-1-4')
insert tc values(1,112,'2009-1-11')select * from ta a left join tb b on a.id = b.id left join tc c on a.id = c.id
where riqi = (select max(riqi) from tc where id = a.id) or riqi is nullid data1 id date2 id data3 riqi
----------- ----------- ----------- ----------- ----------- ----------- -----------------------
1 1 1 11 1 112 2009-01-11 00:00:00.000
2 2 2 22 NULL NULL NULL(2 row(s) affected)