内连接右表中最大日期的记录

[wanghan226]

表table1
id name project
1 a xxxx
2 b ccc
表table12
id name plandate
1 a 2009-09-01
1 a 2009-08-30


select * from table1 inner join table2
on table1.id=table2.id

如何用语句得连接table2中plandate最大的记录即plandate=2009-09-01 的

[小宏]

这个查询在实际中用在哪的啊。。。。

[黄_瓜]

select * from table1 inner join
 (select * from table2 where a not exists 
(select 1 from table2  where id =a.id and name =a.name and plandate <a.plandate )
t
on table1.id=table2.id 

[--小F--]

select 
  * 
from 
  table1 a,
  (select id,name,max(plandate) from table2 group by id,name)b
where 
  a.id=b.id

[黄_瓜]

[Quote=引用 1 楼 beirut 的回复:]
SQL codeselect*from table1innerjoin
(select*from table2where anotexists
(select1from table2where id=a.idand name=a.nameand plandate<a.plandate )
ton table1.id=table2.id
[/Quote]
这个打太快了，有语法错误
看这个

--> 测试数据：@ta
declare @ta table([id] int,[name] varchar(1),[project] varchar(4))
insert @ta
select 1,'a','xxxx' union all
select 2,'b','ccc'

declare @tb table([id] int,[name] varchar(1),[plandate] datetime)
insert @tb
select 1,'a','2009-09-01' union all
select 1,'a','2009-08-30'



select * from @ta  a inner join
 (select * from @tb a where  not exists 
(select 1 from @tb  where id =a.id and name =a.name and plandate <a.plandate ))
t
on a.id=t.id 
/*
id          name project id          name plandate
----------- ---- ------- ----------- ---- -----------------------
1           a    xxxx    1           a    2009-08-30 00:00:00.000

(1 行受影响)


*/

[wujinjian2008n]

[Quote=引用 2 楼 fredrickhu 的回复:]
SQL codeselect*from
table1 a,
(select id,name,max(plandate)from table2groupby id,name)bwhere
a.id=b.id
[/Quote]
up

[lihan6415151528]

create table table1(id int,name varchar(10),project varchar(10))
insert table1 
select 1,'a','xxxx' union all
select 2,'b','ccc'
go


create table table2 (id int,name varchar(10),plandate datetime)
insert table2
select 1,'a','2009-09-01 ' union all
select 1,'a','2009-08-30'
go



select * from table1  a inner join
 (select * from table2 a where  not exists 
(select 1 from table2 where id =a.id and name =a.name and plandate >a.plandate ))
t
on a.id=t.id 


id          name       project    id          name       plandate                                               
----------- ---------- ---------- ----------- ---------- ------------------------------------------------------ 
1           a          xxxx       1           a          2009-09-01 00:00:00.000

（所影响的行数为 1 行）

[黄_瓜]

[Quote=引用 3 楼 beirut 的回复:]
引用 1 楼 beirut 的回复:
SQL codeselect*from table1innerjoin
(select*from table2where anotexists
(select1from table2where id=a.idand name=a.nameand plandate <a.plandate )
ton table1.id=table2.id

这个打太快了，有语法错误
看这个

SQL code--> 测试数据：@tadeclare@tatable([id]int,[name]varchar(1),[project]varchar(4))insert@taselect1,'a','xxxx'unionallselect2,'b','ccc'declare@tbtable([id]int,[name]varchar(1),[plandate]datetime)insert@tbselect1,'a','2009-09-01'unionallselect1,'a','2009-08-30'select*from@ta ainnerjoin
(select*from@tb awherenotexists
(select1from@tbwhere id=a.idand name=a.nameand plandate<a.plandate ))
ton a.id=t.id/*
id name project id name plandate
----------- ---- ------- ----------- ---- -----------------------
1 a xxxx 1 a 2009-08-30 00:00:00.000

(1 行受影响)*/
[/Quote]

--> 测试数据：@ta
declare @ta table([id] int,[name] varchar(1),[project] varchar(4))
insert @ta
select 1,'a','xxxx' union all
select 2,'b','ccc'

declare @tb table([id] int,[name] varchar(1),[plandate] datetime)
insert @tb
select 1,'a','2009-09-01' union all
select 1,'a','2009-08-30'



select * from @ta  a inner join
 (select * from @tb a where  not exists 
(select 1 from @tb  where id =a.id and name =a.name and plandate >a.plandate ))
t
on a.id=t.id 
/*
id          name project id          name plandate
----------- ---- ------- ----------- ---- -----------------------
1           a          xxxx       1           a          2009-09-01 00:00:00.000


(1 行受影响)


*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-03 09:36:33
-- Verstion:
--      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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go 
create table [table1]([id] int,[name] varchar(1),[project] varchar(4))
insert [table1]
select 1,'a','xxxx' union all
select 2,'b','ccc'
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
go 
create table [table2]([id] int,[name] varchar(1),[plandate] datetime)
insert [table2]
select 1,'a','2009-09-01' union all
select 1,'a','2009-08-30'
--------------开始查询--------------------------
select 
  b.* 
from 
  table1 a ,
  (select id,[name],max(plandate) as b from table2 group by id,name)b
where 
  a.id=b.id
----------------结果----------------------------
/* id          name b
----------- ---- -----------------------
1           a    2009-09-01 00:00:00.000

(1 行受影响)
*/

[--小F--]

--重新指定下别名
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-03 09:36:33
-- Verstion:
--      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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go 
create table [table1]([id] int,[name] varchar(1),[project] varchar(4))
insert [table1]
select 1,'a','xxxx' union all
select 2,'b','ccc'
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
go 
create table [table2]([id] int,[name] varchar(1),[plandate] datetime)
insert [table2]
select 1,'a','2009-09-01' union all
select 1,'a','2009-08-30'
--------------开始查询--------------------------
select 
  b.* 
from 
  table1 a ,
  (select id,[name],max(plandate) as plandate from table2 group by id,name)b
where 
  a.id=b.id
----------------结果----------------------------
/* id          name plandate
----------- ---- -----------------------
1           a    2009-09-01 00:00:00.000

(1 行受影响)
*/