表a(学生表)ID Name
1 aa
2 bb
3 cc
4 dd表b(成绩表)ID score date
1 80 2008-3-20 19:11:11
1 90 2010-4-21 16:11:11
1 88 2010-8-24 12:11:11
2 96 2009-10-24 13:10:16
4 69 2010-6-28 10:15:16
5 81 2007-11-24 14:10:16
查询每个学生最近一次考试的成绩。
1 aa
2 bb
3 cc
4 dd表b(成绩表)ID score date
1 80 2008-3-20 19:11:11
1 90 2010-4-21 16:11:11
1 88 2010-8-24 12:11:11
2 96 2009-10-24 13:10:16
4 69 2010-6-28 10:15:16
5 81 2007-11-24 14:10:16
查询每个学生最近一次考试的成绩。
解决方案 »
- MS SQL SERVER group by sql语句
- 请问一些sql代码书写格式的问题
- 遇到了个无法解决的问题,就是数值精度的问题 当我输入 100.100时它却自动变成了 100.1
- 查询某个日期范围的数据,sql的写法哪里有问题?必须要用convert或者datediff或者cast加工处理一下子么?
- 我把“where”当参数传进去,这个存储过程该怎么写?
- 高难度SQL,左外自插入双重不等式连接!!!
- 请教数据库字段类型的更改!
- sql时间比较问题
- 请教有关SQL-Server查询的疑难问题
- 如何删除相同的记录内容?
- 问一句UPDATE的SQL
- [急]请教SQL,查询统计本月用户停留次数
from 表a,表b
where 表a.ID=表b.ID
group by ID,Name,score
select a.[name],(select top 1 score from b where a.id =b.id order by date desc)
from a
from (select *,rownum=row_number() over(order by date desc) from a,b) t
where t.rownum=1sql2000不适用
sql2005可以使用此用法
select * from t
where date in
(select max(date) as date
from t
group by ID)
select a.Id,a.Name,b.Score Where a.Id=b.Id and not exists (select 1 from b b1 where b1.id=b.id And b1.date>b.date)
go
if object_id('test.dbo.a') is not null drop table a
-- 创建数据表
create table a
(
ID int,
Name char(3)
)
go
--插入测试数据
insert into a select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'
go
if object_id('test.dbo.b') is not null drop table b
-- 创建数据表
create table b
(
ID int,
score int,
date datetime
)
go
--插入测试数据
insert into b select 1,80,'2008-3-20 19:11:11'
union all select 1,90,'2010-4-21 16:11:11'
union all select 1,88,'2010-8-24 12:11:11'
union all select 2,96,'2009-10-24 13:10:16'
union all select 4,69,'2010-6-28 10:15:16'
union all select 5,81,'2007-11-24 14:10:16'
go
--代码实现select t.ID,t.Name,tt.score,tt.date
from a t join b tt
on t.ID=tt.ID and not exists(select 1 from b where ID=tt.ID and date>tt.date)/*测试结果ID Name score date
--------------------------------------------------
1 aa 88 2010-08-24 12:11:11.000
2 bb 96 2009-10-24 13:10:16.000
4 dd 69 2010-06-28 10:15:16.000(3 行受影响)
*/
from (
select a.*,rownum=row_number() over(partition by a.id order by a.id,date desc) from a,b
where a.id = b.id
) t
where t.rownum=1
select a.name,b.*
from 表a a left join 表b b on a.id=b.id
where b.date in
(select max(date) as date
from 表b
group by ID)
order by b.ID
2 cc 96 2009-10-24 13:10:16
3 bb NULL NULL NULL
4 dd 69 2010-6-28 10:15:16
2 cc 96 2009-10-24 13:10:16
3 bb NULL NULL NULL
4 dd 69 2010-6-28 10:15:16而不是1 aa 88 2010-8-24 12:11:11
2 cc 96 2009-10-24 13:10:16
4 dd 69 2010-6-28 10:15:16
-- 创建数据表
create table a
(
ID int,
Name char(3)
)
go
--插入测试数据
insert into a select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'
go
if object_id('test.dbo.b') is not null drop table b
-- 创建数据表
create table b
(
ID int,
score int,
date datetime
)
go
--插入测试数据
insert into b select 1,80,'2008-3-20 19:11:11'
union all select 1,90,'2010-4-21 16:11:11'
union all select 1,88,'2010-8-24 12:11:11'
union all select 2,96,'2009-10-24 13:10:16'
union all select 4,69,'2010-6-28 10:15:16'
union all select 5,81,'2007-11-24 14:10:16'
go
--代码实现
select t.*,a.name from a left join b t on a.id=t.id where date=(select max(date) from b where t.id=id) order by t.ID/*测试结果ID score date name
----------- ----------- ----------------------- ----
1 88 2010-08-24 12:11:11.000 aa
2 96 2009-10-24 13:10:16.000 bb
4 69 2010-06-28 10:15:16.000 dd
(3 行受影响)
*/drop table a
drop table b
(
[id] int,
[name] varchar(2)
)
go
insert into #tb1
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'create table #tb2
(
[id] int,
score int,
[date] datetime
)insert into #tb2
select 1,80,'2008-3-20 19:11:11' union all
select 1,90,'2010-4-21 16:11:11' union all
select 1,88,'2010-8-24 12:11:11' union all
select 2,96,'2009-10-24 13:10:16' union all
select 4,80,'2010-6-28 10:15:16' union all
select 5,80,'2007-11-24 14:10:16'select a.id,a.[Name],b.score,b.[date]
from #tb1 a
left join (select id,max(score) score,max(date) date from #tb2 group by id) b on a.id=b.id
Select a.ID, a.Name, b.Score, b.Date from a left join b on a.id = b.id
Where b.date = (select max(date) from b where id = a.id)
order by a.ID
Select c.*, (Select Score from b where id = c.id and Date = c.MaxDate) as score from
(Select a.ID, a.Name, (select max(date) from b where id = a.id) as MaxDate from a) c
Order by c.id
这个才可以显示一次都没考试的学生.....
select a.id,a.name,
(select score from b where b.id=a.id and b.date=t.date) as score,t.date
from a
left outer join
(
select id,max(date) date from b
group by id
) as t on a.id=t.id保管能用