idTable1 Name
1 123
2 13564
3 6946
idTable2 idTable1 name DtInput
1 2 asdfasdf 2008-01-02
2 2 494694169 2008-02-03
3 1 asdfasdf 2008-02-03
4 1 asdfasdf 2008-05-03
5 1 asdfasdf 2008-06-03idTable1 Table1.name idTable2 Table2.name dtInput
1 123 5 asdfasdf 2008-06-03
2 13564 2 494694169 2008-02-03查询出table2中最后一次录入,也就是时间近的,和相应的table1中的信息
--> 测试数据: @s
declare @s table (idTable1 int,Name int)
insert into @s
select 1,123 union all
select 2,13564 union all
select 3,6946
--> 测试数据: @t
declare @t table (idTable2 int,idTable1 int,name varchar(9),DtInput datetime)
insert into @t
select 1,2,'asdfasdf','2008-01-02' union all
select 2,2,'494694169','2008-02-03' union all
select 3,1,'asdfasdf','2008-02-03' union all
select 4,1,'asdfasdf','2008-05-03' union all
select 5,1,'asdfasdf','2008-06-03'select a.idtable1,a.name,idtable2,b.name,dtinput from @s a,@t b
where a.idtable1=b.idtable1 and not exists(select 1 from @t where idtable1=b.idtable1 and DtInput>b.DtInput)
from tb1 a
left join tb2 b
on a.idtable1 = b.idtable1
where not exists(select 1 from tb2 where idtable1 = b.idtable1 and dtinput >b.dtinput)
from table1,table2
where table1.idtable1 = table2.idtable2
group by table1.idtable1,table1.idtable1.name,table2.idtable2,table2.idtable2.name
insert @ta
select 1, '123' union all
select 2, '13564' union all
select 3, '6946' declare @tb table(id int,id2 int,name varchar(20),dtinput smalldatetime)
insert @tb
select 1, 2, 'asdfasdf', '2008-01-02' union all
select 2 ,2, '494694169', '2008-02-03' union all
select 3, 1, 'asdfasdf', '2008-02-03' union all
select 4 ,1, 'asdfasdf', '2008-05-03' union all
select 5, 1, 'asdfasdf', '2008-06-03'
select a.id,a.name,b.id,b.name,dtinput
from @tb as b join @ta as a on a.id=b.id2
where not exists(select 1 from @tb where id2=b.id2 and dtinput>b.dtinput)
order by id2
/*
id name id name dtinput
----------- ---------- ----------- -------------------- ------------------------------------------------------
1 123 5 asdfasdf 2008-06-03 00:00:00
2 13564 2 494694169 2008-02-03 00:00:00(2 row(s) affected)
*/
from table1,talbe2
where table1.idtable1=table2.idtable1 and dtinput in(select max(dtinput) from table2)
declare @ta table(id int, name varchar(10))
insert @ta
select 1, '123' union all
select 2, '13564' union all
select 3, '6946' declare @tb table(id int,id2 int,name varchar(20),dtinput smalldatetime)
insert @tb
select 1, 2, 'asdfasdf', '2008-01-02' union all
select 2 ,2, '494694169', '2008-02-03' union all
select 3, 1, 'asdfasdf', '2008-02-03' union all
select 4 ,1, 'asdfasdf', '2008-05-03' union all
select 5, 1, 'asdfasdf', '2008-06-03' select a.id,a.name ,b.id,b.name, c.dtInput from
(select id2,max(dtInput) as dtInput
from @tb
group by id2) c
left outer join @ta a on a.id=c.id2
left outer join @tb b on b.id2=c.id2 and b.dtInput=c.dtInputid name id name dtInput
----------- ---------- ----------- -------------------- ------------------------------------------------------
1 123 5 asdfasdf 2008-06-03 00:00:00
2 13564 2 494694169 2008-02-03 00:00:00(所影响的行数为 2 行)
from @tb as b join @ta as a
on a.id=b.id2 and b.dtinput=(select MAX(dtinput) from @tb where id2=b.id2)
order by id2
Set Nocount On
declare @1 table([idTable1] int,[Name] int)
Insert @1
select 1,123 union all
select 2,13564 union all
select 3,6946declare @2 table([idTable2] int,[idTable1] int,[name] nvarchar(9),[DtInput] Datetime)
Insert @2
select 1,2,N'asdfasdf','2008-01-02' union all
select 2,2,N'494694169','2008-02-03' union all
select 3,1,N'asdfasdf','2008-02-03' union all
select 4,1,N'asdfasdf','2008-05-03' union all
select 5,1,N'asdfasdf','2008-06-03'
Select a.idTable1,a.Name,b.name,b.DtInput
from @1 a
Inner Join @2 b On b.idTable1=a.idTable1 And
b.idTable2=(Select Max(idTable2) From @2 Where idTable1=b.idTable1)
Order By a.idTable1/*
idTable1 Name name DtInput
----------- ----------- --------- -----------------------
1 123 asdfasdf 2008-06-03 00:00:00.000
2 13564 494694169 2008-02-03 00:00:00.000
*/
from @1 a
Inner Join @2 b On b.idTable1=a.idTable1 And
b.idTable2=(Select Max(idTable2) From @2 Where idTable1=b.idTable1)
Order By a.idTable1
if object_id('t') is not null
drop table t
go
if object_id('tb') is not null
drop table tb
create table t (idTable1 int, Name varchar(10))
insert into t select 1,'123'
union all select 2,'13564'
union all select 3,'6946'
go
create table tb(idTable2 int,idTable1 int,name varchar(10),DtInput datetime)
insert into tb select 1,2,'asdfasdf','2008-01-02'
union all select 2,2,'494694169','2008-02-03'
union all select 3,1 ,'asdfasdf','2008-02-03'
union all select 4,1,'asdfasdf','2008-05-03'
union all select 5,1,'asdfasdf','2008-06-03'
go
select t.idTable1,t.Name as Table1_name,max(tb.idTable2) as idTable2,
max(tb.name) as Table2_name,max(tb.DtInput)as dtInput
from t join tb on t.idTable1=tb.idTable1
group by t.idTable1,t.Name1 123 5 asdfasdf 2008-06-03 00:00:00.000
2 13564 2 asdfasdf 2008-02-03 00:00:00.000
table2 a inner join table1 b on a.idtable2=b.idtable1 where exists if(select 1 from table2 where idtable2=a.idtable2 and dtinput>a.dtinput)