各位帮忙一下，。。。sqL~~~~~~~~

[358087202]

tb_A 表:用户表
--------------
account name
ap\test1 测试1
ap\test2 测试2
ap\test3 测试3

---------------
tb_B 表：操作记录表
account , bk_id, date,Colum1,Colum2,Colum3...
ap\test1 14 2008-1-1
ap\test1 15 2008-1-7
ap\test1 16 2008-1-5
ap\test1 17 2008-1-2
ap\test2 20 2008-11-2
ap\test3 19 2008-2-2


--------------------------------------------
需要的结果:ab表,Join，同一个account取最大一条b.bk_id记录

a.account a.name b.bk_id b.date,.. b.Colum1,b.Colum2,b.Colum3...N多字段
----------------------------------------------------------
ap\test1 测试1 17 2008-1-2 .............
----------------------------------------------------------
ap\test2...................................................

[csdyyr]

select *
from tb_a as a join tb_b as b on a.ccount=b.account
where not exists(select 1 from tb_b where bk_id>b.bk_id)

[358087202]

谢谢楼上回复，但是还是不对，总记录应该是382条

Select a.*
FROM Member as a JOIN BookReadLog as b
on a.account = b.account
where not exists(select 1 from BookReadLog where bookid>b.bookid) 结果---3


select count(*) from Member 结果-----382

[pengxuan]

if object_id('tb_A') is not null
   drop table tb_A
go
create table tb_A(account varchar(10),name varchar(10))
go
insert into tb_A
select 'ap\test1','测试1' union all
select 'ap\test2','测试2' union all
select 'ap\test3','测试3'
go
if object_id('tb_B') is not null
   drop table tb_B
go
create table tb_B(account varchar(10),bk_id int,date varchar(10))
go
insert into tb_B
select 'ap\test1',14,'2008-1-1' union all
select 'ap\test1',15,'2008-1-7' union all
select 'ap\test1',16,'2008-1-5' union all
select 'ap\test1',17,'2008-1-2' union all
select 'ap\test2',20,'2008-12-2' union all
select 'ap\test3',19,'2008-2-2'
go
select tb_a.account,name,c.date from tb_A inner join(
select * from tb_B b where not exists(select 1 from tb_B where account=b.account and bk_id>b.bk_id)) c on tb_a.account=c.account

[358087202]

谢谢，两位。。。