求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
两表关联查询
表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
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)
B.*
FROM
AT1 A,BT1 B
WHERE
A.A1=B.BT1
AND
B.BT3=(SELECT MAX(BT3) FROM BT1 WHERE BT1=B.BT1)
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 行受影响)
但是为了更安全,这样查询
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