根据上面的表格我想查询studentID和courseID对应得最高分、最低分、平均分和最高分的时间、最低分的时间我只能找到studentID和courseID对应得最高分、最低分、平均分,不知道怎么查他们对应的时间。这是我写的:
select StudentID,CourseID,max(Grade) maxGrade,min(grade) minGrade,avg(grade) avgGrade
from score
group by score.StudentID,score.CourseID
我想要的结果是:studentID,CourseID,maxGrade,maxGradeTime,minGrade minGradeTime,avgGrade用linq实现也行
select StudentID,CourseID,max(Grade) maxGrade,min(grade) minGrade,avg(grade) avgGrade
from score
group by score.StudentID,score.CourseID
我想要的结果是:studentID,CourseID,maxGrade,maxGradeTime,minGrade minGradeTime,avgGrade用linq实现也行
insert into tb select 1,1,78,'2011-09-05 00:00:01.000'
insert into tb select 1,1,67,'2011-09-05 00:00:41.000'
insert into tb select 1,1,90,'2011-09-05 00:00:51.000'
insert into tb select 2,1,56,'2011-09-05 00:01:09.000'
insert into tb select 2,1,77,'2011-09-05 00:01:39.000'
insert into tb select 2,1,84,'2011-09-05 00:01:59.000'
go
select a.*,b.dt as maxdt,c.dt as mindt from(
select sid,cid,max(grade)maxg,min(grade)ming,avg(grade)avgg from tb group by sid,cid
)a inner join tb b on a.sid=b.sid and a.cid=b.cid and a.maxg=b.grade
inner join tb c on a.sid=c.sid and a.cid=c.cid and a.ming=c.grade
/*
sid cid maxg ming avgg maxdt mindt
----------- ----------- ----------- ----------- ----------- ----------------------- -----------------------
1 1 90 67 78 2011-09-05 00:00:51.000 2011-09-05 00:00:41.000
2 1 84 56 72 2011-09-05 00:01:59.000 2011-09-05 00:01:09.000(2 行受影响)*/
go
drop table tb