表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
select * from table1 a, (select id,name,max(plandate) from table2 group by id,name)b where a.id=b.id
这个打太快了,有语法错误 看这个--> 测试数据:@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 行受影响) */
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' goselect * 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 行)
--> 测试数据:@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 行受影响) */
---------------------------------------------------------------- -- 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 行受影响) */
--重新指定下别名 ---------------------------------------------------------------- -- 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 行受影响) */
(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
*
from
table1 a,
(select id,name,max(plandate) from table2 group by id,name)b
where
a.id=b.id
看这个--> 测试数据:@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 行受影响)
*/
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'
goselect * 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 行)
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 行受影响)
*/
-- 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 行受影响)
*/
----------------------------------------------------------------
-- 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 行受影响)
*/