表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
2 90 2004-10-24 13:10:16
4 69 2010-6-28 10:15:16
5 81 2007-11-24 14:10:16
查询每个学生最近一次考试的成绩。如果没有成绩用NULL表示
请注意我搜索希望得到的结果是:1 aa 88 2010-8-24 12:11:11
2 bb 96 2009-10-24 13:10:16
3 cc NULL NULL NULL
4 dd 69 2010-6-28 10:15:16而不是1 aa 88 2010-8-24 12:11:11
2 bb 96 2009-10-24 13:10:16
4 dd 69 2010-6-28 10:15: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
2 90 2004-10-24 13:10:16
4 69 2010-6-28 10:15:16
5 81 2007-11-24 14:10:16
查询每个学生最近一次考试的成绩。如果没有成绩用NULL表示
请注意我搜索希望得到的结果是:1 aa 88 2010-8-24 12:11:11
2 bb 96 2009-10-24 13:10:16
3 cc NULL NULL NULL
4 dd 69 2010-6-28 10:15:16而不是1 aa 88 2010-8-24 12:11:11
2 bb 96 2009-10-24 13:10:16
4 dd 69 2010-6-28 10:15:16
drop table ta
Go
Create table ta([ID] int,[Name] nvarchar(2))
Insert ta
select 1,N'aa' union all
select 2,N'bb' union all
select 3,N'cc' union all
select 4,N'dd'
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[score] int,[date] datetime)
Insert tb
select 1,80,N'2008-3-20 19:11:11' union all
select 1,90,N'2010-4-21 16:11:11' union all
select 1,88,N'2010-8-24 12:11:11' union all
select 2,96,N'2009-10-24 13:10:16' union all
select 2,90,N'2004-10-24 13:10:16' union all
select 4,69,N'2010-6-28 10:15:16' union all
select 5,81,N'2007-11-24 14:10:16'
Go
select a.*,
b.[score],
b.[date]
from ta a left join tb b on a.id=b.id
where not exists(select 1
from tb
where id=b.id and date>b.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
3 cc NULL NULL
4 dd 69 2010-06-28 10:15:16.000(4 行受影响)*/
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(ID int, Name varchar(8))
insert into #a
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'cc' union all
select 4, 'dd'
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(ID int, score int, date datetime)
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 2, 90, '2004-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'select * from #a a left join #b b on a.id=b.id where not exists (select 1 from #b where id=a.id and date>b.date)/*
ID Name ID score date
----------- -------- ----------- ----------- -----------------------
1 aa 1 88 2010-08-24 12:11:11.000
2 bb 2 96 2009-10-24 13:10:16.000
3 cc NULL NULL NULL
4 dd 4 69 2010-06-28 10:15:16.000
*/
select a.id,a.name,b.score,b.date
from 表a a left join
(select *
from 表b
where date in
(select max(date) as date
from 表b
group by ID)) b on a.id=b.id
只是
(select *
from 表b
where date in
(select max(date) as date
from 表b
group by ID)) b on a.id=b.id
不用这么麻烦
只需
(select ID,max(date) as date
from 表b
group by ID) b on a.id=b.id
if object_id('tb') is not null drop table tb
create table tb(id int,name varchar(10)
insert into tb
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'--测试数据创建表tb1
if object_id('tb1') is not null drop table tb1
create table tb1 (id int,score int,date datetime)
inset into tb1
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,90,'2009-10-24' 13:10:16 union all
select 2,90,'2004-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'
--查询语句
select * from tb a left join tb1 b on a.id=b.id
where not exists(select 1 from b where id=a.id and date>b.date)--结果
/*
name id score date
------- --------- ------------ -----------------------------------
aa 1 88 2010-08-24 12:11:11.000
bb 2 96 2009-10-24 10:10:16.000
cc NULL NULL NULL
dd 4 69 2010-06-28 10:15:16.000
*/
if object_id('tb') is not null drop table tb
create table tb(id int,name varchar(10)
insert into tb
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'--测试数据创建表tb1
if object_id('tb1') is not null drop table tb1
create table tb1 (id int,score int,date datetime)
inset into tb1
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,90,'2009-10-24 13:10:16' union all
select 2,90,'2004-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'
--查询语句
select * from tb a left join tb1 b on a.id=b.id
where not exists(select 1 from b where id=a.id and date>b.date)--结果
[name] id score date
------- --------- ------------ -----------------------------------
aa 1 88 2010-08-24 12:11:11.000
bb 2 96 2009-10-24 10:10:16.000
cc NULL NULL NULL
dd 4 69 2010-06-28 10:15:16.000