查询每一个成员的最后一条记录的集合

Question

学生表
ID Name
1 甲
2 乙
3 丙成绩类型表
ID Name
1 通过
2 补考
3 挂科成绩历史表
ID SID GID Date
1 1 1 2010-9-1
2 2 2 2010-9-1
3 3 2 2010-9-1
4 2 1 2010-9-8
5 3 3 2010-9-8有以上三个表，希望查询获得下面这个表，求助！
最终成绩表
ID SName GName Date
1 甲 通过 2010-9-1
2 乙 通过 2010-9-8
3 丙 挂科 2010-9-8

Answer 1

create table #student1
(
  ID int identity(1,1) primary key,
  [Name] nvarchar(20)
)
insert into #student1 select '甲'
union all select '乙'
union all select '丙'create table #Score1
(
  ID int identity(1,1) primary key,
  [Name] nvarchar(20)
)
insert into #Score1 select '通过'
union all select '补考'
union all select '挂科'create table #Subject1
(
  ID int identity(1,1) primary key,
  SID int,
  GID int,
  Date datetime
)
insert into #Subject1 select 1,1,'2010-9-1'
union all select 2,2,'2010-9-1'
union all select 3,2,'2010-9-1'
union all select 2,1,'2010-9-8'
union all select 3,3,'2010-9-8'select S.ID,S.[Name] as SName,S1.[Name] as GName,S2.Date
from #student1 S
join
(
 select * from #Subject1 S where not
exists(select * from #Subject1 where SID=S.SID and Date>S.Date)
) S2
on S.ID=S2.SID
join
#Score1 S1
on S1.ID=S2.GIDID          SName                GName                Date
----------- -------------------- -------------------- -----------------------
1           甲                    通过                   2010-09-01 00:00:00.000
2           乙                    通过                   2010-09-08 00:00:00.000
3           丙                    挂科                   2010-09-08 00:00:00.000(3 行受影响)

Answer 2

create table #student1
(
  ID int identity(1,1) primary key,
  [Name] nvarchar(20)
)
insert into #student1 select '甲'
union all select '乙'
union all select '丙'create table #Score1
(
  ID int identity(1,1) primary key,
  [Name] nvarchar(20)
)
insert into #Score1 select '通过'
union all select '补考'
union all select '挂科'create table #Subject1
(
  ID int identity(1,1) primary key,
  SID int,
  GID int,
  Date datetime
)
insert into #Subject1 select 1,1,'2010-9-1'
union all select 2,2,'2010-9-1'
union all select 3,2,'2010-9-1'
union all select 2,1,'2010-9-8'
union all select 3,3,'2010-9-8'select S.ID,S.[Name] as SName,S1.[Name] as GName,convert(varchar(10),S2.Date,120) as Date
from #student1 S
join
(
 select * from #Subject1 S where not
exists(select * from #Subject1 where SID=S.SID and Date>S.Date)
) S2
on S.ID=S2.SID
join
#Score1 S1
on S1.ID=S2.GIDID          SName                GName                Date
----------- -------------------- -------------------- ----------
1           甲                    通过                   2010-09-01
2           乙                    通过                   2010-09-08
3           丙                    挂科                   2010-09-08(3 行受影响)

Answer 3

DECLARE @stu TABLE(id INT,NAME VARCHAR(30))
DECLARE @stp TABLE(id INT,NAME VARCHAR(30))
DECLARE @scr TABLE(id INT,sid INT,gid INT,date DATETIME)INSERT INTO @stu(id,NAME)
select 1,'甲' union all
select 2,'乙' union all
select 3,'丙'INSERT INTO @stp(id,NAME)
select 1,'通过' union all
select 2,'补考' union all
select 3,'挂科'INSERT INTO @scr(id,sid,gid,date)
select 1,1,1,'2010-9-1' union all
select 2,2,2,'2010-9-1' union all
select 3,3,2,'2010-9-1' union all
select 4,2,1,'2010-9-8' union all
select 5,3,3,'2010-9-8'SELECT s.sid AS ID,u.NAME AS SName,t.NAME AS GName,CONVERT(VARCHAR(10),s.date,120) AS Date FROM @scr AS s LEFT JOIN @stp AS t ON s.gid = t.id INNER JOIN @stu AS u ON s.sid = u.id WHERE NOT EXISTS(SELECT 1 FROM @scr AS ss WHERE s.sid = ss.sid AND s.date < ss.date)
/*
ID          SName                          GName                          Date       
----------- ------------------------------ ------------------------------ ---------- 
1           甲                              通过                             2010-09-01
2           乙                              通过                             2010-09-08
3           丙                              挂科                             2010-09-08
*/

Answer 4

--> 测试数据: [学生表]
if object_id('[学生表]') is not null drop table [学生表]
go
create table [学生表] (ID int,Name varchar(2))
insert into [学生表]
select 1,'甲' union all
select 2,'乙' union all
select 3,'丙'
--> 测试数据: [成绩类型表]
if object_id('[成绩类型表]') is not null drop table [成绩类型表]
go
create table [成绩类型表] (ID int,Name varchar(4))
insert into [成绩类型表]
select 1,'通过' union all
select 2,'补考' union all
select 3,'挂科'
--> 测试数据: [成绩历史表]
if object_id('[成绩历史表]') is not null drop table [成绩历史表]
go
create table [成绩历史表] (ID int,SID int,GID int,Date datetime)
insert into [成绩历史表]
select 1,1,1,'2010-9-1' union all
select 2,2,2,'2010-9-1' union all
select 3,3,2,'2010-9-1' union all
select 4,2,1,'2010-9-8' union all
select 5,3,3,'2010-9-8'select SName=a.name, 
GName=b.name,
Date
from [学生表] a,
[成绩类型表] b,
[成绩历史表] c
where a.id=sid and b.id=gid
and not exists(select 1 from [成绩历史表] where sid=c.sid and date>c.date)
SName GName Date
----- ----- -----------------------
甲     通过    2010-09-01 00:00:00.000
乙     通过    2010-09-08 00:00:00.000
丙     挂科    2010-09-08 00:00:00.000(3 行受影响)

Answer 5

抱歉，我还在论坛上搜索是否有可用信息。
ps：能否改个Access 2007能用的查询语句？我用的这个。

Answer 6

select S.ID,S.[Name] as SName,S1.[Name] as GName,S2.Date
from #student1 S
join
(
 select * from #Subject1 S where not
exists(select * from #Subject1 where SID=S.SID and Date>S.Date)
) S2
on S.ID=S2.SID
join
#Score1 S1
on S1.ID=S2.GID这个在 access里面不能执行成功吗？
没有用到sql2005的内置函数了？

Answer 7

应该是确实不行，Access里面貌似不能有‘#’

Answer 8

你把表名称前面的# 去掉这个是sql中的临时表

Answer 9

去掉后报“FORM 子句语法错误”，定位到“JOIN”上

Answer 10

select S.ID,S.[Name] as SName,S1.[Name] as GName,S2.Date
from student1 S
join
(
 select * from Subject1 S where not
exists(select * from Subject1 where SID=S.SID and Date>S.Date)
) S2
on S.ID=S2.SID
join
Score1 S1
on S1.ID=S2.GID

Answer 11

你的access里面有表
student1 
Subject1 
Score1 
吗？

Answer 12

用这个
我帮你用Access2007写了个
SELECT a.name as SName, b.name as GName, Date
FROM student AS a, Score AS b, Subject AS c
WHERE a.id=sid and b.id=gid
and not exists(select 1 from Subject where sid=c.sid and date>c.date);

Answer 13

student--》学生表
Score-->成绩类型
Subject -->成绩历史表

Answer 14

能说明下吗？ WHERE 后的看不懂..3.

Answer 15

and not exists(select 1 from Subject where sid=c.sid and date>c.date);成绩历史表与自己比较查询
学生ID相同,时间最大的信息

Answer 16

SELECT a.name as SName, b.name as GName, Date
FROM student AS a, Score AS b, Subject AS c
WHERE a.id=sid and b.id=gid
and not exists(
select 1 from Subject 
where (sid=c.sid) and ((date > c.date) OR ((date = c.date) AND (ID > c.ID))));
追加了个条件，然则查询速度超慢...我的数据样本为20000，每个成员有0~1000条记录不等