select A,B,C=(Select Max(C) From Tb2 where TB1.A=TB2.A and TB1.B=Tb2.B) From TB1
select a.*,b.c from a join (select * from tb t where not exists(select 1 from tb where a=t.a and b=t.b and c>t.c) on a.a=b.a and a.b=b.b
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-11-10 22:13:38 -- Version: -- 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.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([A] varchar(2),[B] varchar(2)) insert [a] select 'm1','N1' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([A] varchar(2),[B] varchar(2),[C] datetime) insert [b] select 'm1','N1','2009-1-1' union all select 'm1','N1','2009-2-5' union all select 'm1','N1','2009-6-1' --------------开始查询-------------------------- select a.*,b.c from a join (select * from b t where not exists(select 1 from b where a=t.a and b=t.b and c>t.c))b on a.a=b.a and a.b=b.b ----------------结果---------------------------- /* A B c ---- ---- ----------------------- m1 N1 2009-06-01 00:00:00.000(1 行受影响) */
Select A,B,C=Max(C) From T2 Group By A,B
SELECT B.* FROM A,B WHERE A.A=B.A AND A.B=B.B AND NOT EXISTS(SELECT 1 FROM B WHERE A=B.A AND B=B.B AND C>B.C)
select t1.*,t2.c from t1,t2 where t2.c in(select top 1 c from t2 order by c desc) and t1.a=t2.a and t1.b=t2.b
我怎么觉得你们想复杂了?create table [t1]([A] varchar(2),[B] varchar(2)) insert [t1] select 'm1','N1' create table [t2]([A] varchar(2),[B] varchar(2),[C] datetime) insert [t2] select 'm1','N1','2009-1-1' union all select 'm1','N1','2009-2-5' union all select 'm1','N1','2009-6-1'select t1.* , max(c) c from t1 , t2 where t1.a = t2.a and t1.b = t2.b group by t1.a , t1.bdrop table t1 , t2/* A B c ---- ---- ------------------------------------------------------ m1 N1 2009-06-01 00:00:00.000 */
select a.*,t.c from a join (select a,b,max(c) as c from b group by a,b) ton a.a=t.a and a.b=t.b
select t1.a,t1.b,max(t2.c) from t1 inner join t2 on t1.a=t2.a and t1.b=t2.b group by t1.a,t1.b
select A,B,C=(Select Max(C) From Tb2 where TB1.A=TB2.A and TB1.B=Tb2.B)
From TB1
a.*,b.c
from
a
join
(select * from tb t where not exists(select 1 from tb where a=t.a and b=t.b and c>t.c)
on
a.a=b.a and a.b=b.b
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-10 22:13:38
-- Version:
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([A] varchar(2),[B] varchar(2))
insert [a]
select 'm1','N1'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([A] varchar(2),[B] varchar(2),[C] datetime)
insert [b]
select 'm1','N1','2009-1-1' union all
select 'm1','N1','2009-2-5' union all
select 'm1','N1','2009-6-1'
--------------开始查询--------------------------
select
a.*,b.c
from
a
join
(select * from b t where not exists(select 1 from b where a=t.a and b=t.b and c>t.c))b
on
a.a=b.a and a.b=b.b
----------------结果----------------------------
/* A B c
---- ---- -----------------------
m1 N1 2009-06-01 00:00:00.000(1 行受影响)
*/
From T2
Group By A,B
WHERE
A.A=B.A AND A.B=B.B
AND NOT EXISTS(SELECT 1 FROM B WHERE A=B.A AND B=B.B AND C>B.C)
select t1.*,t2.c from t1,t2
where t2.c in(select top 1 c from t2 order by c desc) and t1.a=t2.a and t1.b=t2.b
我怎么觉得你们想复杂了?create table [t1]([A] varchar(2),[B] varchar(2))
insert [t1]
select 'm1','N1'
create table [t2]([A] varchar(2),[B] varchar(2),[C] datetime)
insert [t2]
select 'm1','N1','2009-1-1' union all
select 'm1','N1','2009-2-5' union all
select 'm1','N1','2009-6-1'select t1.* , max(c) c from t1 , t2 where t1.a = t2.a and t1.b = t2.b group by t1.a , t1.bdrop table t1 , t2/*
A B c
---- ---- ------------------------------------------------------
m1 N1 2009-06-01 00:00:00.000
*/
a.*,t.c
from
a
join
(select a,b,max(c) as c from b group by a,b) ton
a.a=t.a and a.b=t.b
from t1 inner join t2 on t1.a=t2.a and t1.b=t2.b
group by t1.a,t1.b