一个筛选的SQL语句

[muyebo]

table A
NUM ID
1 BBB
2 AAA
3 BBB
4 BBB

TABLE b
NUM ISLATE time
1 TRUE 08-02
2 FALSE 08-23
3 TRUE 09-14
4 FALSE 06-23

问 如何找出最后一次（time）为true的所有ID数据呢
关联用num
即最后一次都没迟到的人。

[pt1314917]

结果应该是啥？

[csuxp2008]

每个人最后一次？

select id
from a
where num 
in 
		(	select num 
			from
			(
			 select b.*,row_number() over(partition by num order by time desc) rn
			 from b
			) c
			where rn=1 and islate ='true'
		)

[GUOCHENGJUN]

select c.id from a c inner join (select a.* from b a where exists(select * from b where time>=a.time) and a.ISLATE=false ) d
on c.num=d.num

[GUOCHENGJUN]

IF NOT OBJECT_ID('A') IS NULL
DROP TABLE A
GO
IF NOT OBJECT_ID('B') IS NULL
DROP TABLE B
GO
create table A (num int,id char(30))
insert into A 
select 1,'BBB' union all
select 2,'AAA'union all
select 3,'BBB' union all
select 4,'BBB'
go 
create table B
(NUM int ,ISLATE CHAR(10) ,TIME datetime)

go
insert into B
select 1,'TRUE','2010-7-31'union all
select 2,'FALSE','2010-08-23' UNION ALL
SELECT 3,'TRUE','2010-09-04'UNION ALL
SELECT 4,'TRUE','2010-06-23'

select c.id from a c inner join (select a.* from b a where exists(select * from b where time>=a.time) and a.ISLATE='false ') d
on c.num=d.num
GO
--id
--AAA                           

[dawugui]

select m.* , n.* from a m , b n
where m.num = n.num and n.ISLATE = 'true' and n.time = (select max(time) from b where num = n.num and ISLATE = 'true')

select m.* , n.* from a m , b n
where m.num = n.num and n.ISLATE = 'true' and not exists (select 1 from b where num = n.num and ISLATE = 'true' and time > n.time)

select m.* , n.* from a m left join b n
on m.num = n.num and n.ISLATE = 'true' and n.time = (select max(time) from b where num = n.num and ISLATE = 'true')

select m.* , n.* from a m left join b n
on m.num = n.num and n.ISLATE = 'true' and not exists (select 1 from b where num = n.num and ISLATE = 'true' and time > n.time)

[cxmcxm]

select  * from 
a where id in 
	(select b.id from b
	(select id,max(time) time from b group by id) c where b.id=c.id and b.time=c.time and b.islate='true')

[iamltd]

从题意来看2楼就搞定了啊

[Quote=引用 2 楼 csuxp2008 的回复:]
每个人最后一次？

SQL code

select id
from a
where num
in
( select num
from
(
select b.*,row_number() over(partition by num order by time desc) rn……
[/Quote]