求SQL句，两表一对多，只取日期最大的一条记录，

[terry三]

求SQL句，两表一对多，只取日期最大的一条记录，
两表关联查询
表AT1
字段A1
A
B


表BT1
BT1 BT2 BT3
A 1.5 2010-05-06
A 1.6 2010-10-03
B 15 2009-09-06
A 1.8 2011-08-01
B 16 2010-10-07


结果
A 1.8 2011-08-01
B 16 2010-10-07

[苦苦的潜行者]

你的问题偷懒的方式是不需要多表查询的
但是为了更安全,这样查询

create table AT1(A1 varchar(10))
insert AT1
select 'A' union 
select 'B' union
select 'D'
create table BT1(BT1 varchar(10),BT2 float,BT3 datetime)
insert BT1 
select 'A', 1.5, '2010-05-06' union all
select 'A', 1.6, '2010-10-03' union all
select 'B', 15, '2009-09-06' union all
select 'A', 1.8, '2011-08-01' union all
select 'C', 1.8, '2011-08-01' union all
select 'D', 1.8, '2011-08-01' union all
select 'B', 16, '2010-10-07'
go
select o.* from BT1 as o
join AT1 as p on o.BT1=p.A1  
where not exists(select 1 from BT1 where o.BT3<BT3 and o.BT1 =BT1 )
order by o.BT1 
/*
BT1  BT2      BT3
--- ---   --------------
A	1.8	2011-08-01 00:00:00.000
B	16	2010-10-07 00:00:00.000
D	1.8	2011-08-01 00:00:00.000
*/
go
drop table AT1,BT1

[q806294478]

SELECT * FROM BT1 A WHERE BT3=(SELECT MAX(BT3) FROM BT1 WHERE BT1=A.BT1)

[JayDa214]

nnd太长见识了！！！！！

[ericlanmo]

太厉害了吧 这样也可以

[ericlanmo]

SELECT * FROM BT1 A WHERE BT3=(SELECT MAX(BT3) FROM BT1 WHERE BT1=A.BT1)

[SQL77]

SELECT * FROM BT1 B WHERE BT3=(SELECT MAX(BT3) FROM BT1 WHERE BT1=B.BT1)

[-晴天]

select a.*,b.* 
from at1 a inner join bt1 b on a.a1=b.bt1
where not exists(select 1 from bt1 where bt1=b.bt1 and bt3>b.bt3)

[--小F--]

SELECT
  B.*
FROM
  AT1 A,BT1 B
WHERE
  A.A1=B.BT1
AND
  B.BT3=(SELECT MAX(BT3) FROM BT1 WHERE BT1=B.BT1)

[百年树人]

select *
from bt1 t
where not exists(select 1 from bt1 where bt1=t.bt1 and bt3>t.bt3)

[叶子]

declare @表BT1 table (BT1 varchar(1),BT2 numeric(4,1),BT3 VARCHAR(10))
insert into @表BT1
select 'A',1.5,'2010-05-06' union all
select 'A',1.6,'2010-10-03' union all
select 'B',15,'2009-09-06' union all
select 'A',1.8,'2011-08-01' union all
select 'B',16,'2010-10-07'

select * from @表BT1 a
WHERE BT3=(SELECT MAX(BT3) FROM @表BT1 WHERE BT1=a.BT1)
/*
BT1  BT2                                     BT3
---- --------------------------------------- ----------
B    16.0                                    2010-10-07
A    1.8                                     2011-08-01
*/

[勿勿]

 if OBJECT_ID('tb') is not null 
 drop table tb 
 go 
  create    table tb(BT1 varchar(1),BT2 numeric(4,1),BT3 VARCHAR(10))
insert into tb
select 'A',1.5,'2010-05-06' union all
select 'A',1.6,'2010-10-03' union all
select 'B',15,'2009-09-06' union all
select 'A',1.8,'2011-08-01' union all
select 'B',16,'2010-10-07'
select * from tb b WHERE BT3=(SELECT MAX(BT3) FROM tb    WHERE BT1=b.BT1)
 


(5 行受影响)
BT1  BT2                                     BT3
---- --------------------------------------- ----------
B    16.0                                    2010-10-07
A    1.8                                     2011-08-01

(2 行受影响)

[Anitee]