送分题 求一个sql语句

[超级管理员9527]

表A id name
1 张三
2 李四

表B id rid(A.id) date result
1 1 2009.1.1 100
2 2 2009.1.1 89
3 1 2009.2.1 99
4 2 2009.2.1 90

求最后一次考试时间在2009.1.10-2009.2.10之间的人的名子

[ws_hgo]

jf

[ws_hgo]

[Quote=引用楼主 crybird 的回复:]
表A id name
1 张三
2 李四

表B id rid(A.id) date    result
    1    1      2009.1.1 100
    2    2      2009.1.1 89
    3    1      2009.2.1 99
    4    2      2009.2.1 90

求最后一次考试时间在2009.1.10-2009.2.10之间的人的名子
[/Quote]

select A.name from A join
(
select * from B where date between '2009.1.10' and '2009.2.10'
)B on A.id=B.rid

[navy887]

..

[dawugui]

[Quote=引用楼主 crybird 的回复:]
表A id name
1 张三
2 李四

表B id rid(A.id) date    result
    1    1      2009.1.1 100
    2    2      2009.1.1 89
    3    1      2009.2.1 99
    4    2      2009.2.1 90

求最后一次考试时间在2009.1.10-2009.2.10之间的人的名子
[/Quote]

select a.name from a where id in
(select t.rid from b t where date between '2009.1.10' and '2009.2.10' and date = (select max(date) from b where rid = t.rid and date between '2009.1.10' and '2009.2.10') )

[--小F--]

select 
 a.name 
from 
 a,b
where 
 a.id=b.rid
and
 b.date between '2009.1.10' and '2009.2.10'

[--小F--]

最后一次考试的成绩啊 看来分拿不了了 惭愧

[dawugui]

create table A (id int,name varchar(10))
insert into a values(1, '张三') 
insert into a values(2, '李四') 
create table B (id int,rid int,date datetime,   result int)
insert into b values(1 ,   1  ,    '2009.1.1', 100 )
insert into b values(2 ,   2  ,    '2009.1.1', 89 )
insert into b values(3 ,   1  ,    '2009.2.1', 99 )
insert into b values(4 ,   2  ,    '2009.2.1', 90 )
go

select a.name from a where id in
(select t.rid from b t where date between '2009.1.10' and '2009.2.10' and date = (select max(date) from b where rid = t.rid and date between '2009.1.10' and '2009.2.10') )
/*
name       
---------- 
张三
李四

（所影响的行数为 2 行）
*/

select a.*,m.* from a ,
(select t.* from b t where date between '2009.1.10' and '2009.2.10' and date = (select max(date) from b where rid = t.rid and date between '2009.1.10' and '2009.2.10') ) m
where a.id = m.rid
order by a.id
/*
id          name       id          rid         date                                                   result      
----------- ---------- ----------- ----------- ------------------------------------------------------ ----------- 
1           张三         3           1           2009-02-01 00:00:00.000                                99
2           李四         4           2           2009-02-01 00:00:00.000                                90

（所影响的行数为 2 行）
*/

drop table a , b

[chuifengde]

SELECT * FROM A AAA inner join 
(select * from B BB WHERE NOT EXISTS(SELECT 1 FROM B WHERE rid=BB.rid AND [date]>BB.[Date])) BBB
ON aaa.Id=BBB.id 
where BBB.[Date] between '2009.1.10' and '2009.2.10'

[shentanyi]

select a.name from 表A as a inner join (select * from 表B) b on a.id=b.rid where date between '2009.1.1' and '2009.2.10'