问题描述
我有一张桌子,该桌子可以跟踪课程的出勤情况.列是课程编号,课程,人员编号和日期.我有一个查询(如下),该查询提取一个人出现的最早日期以及相关的课程,课程和人格信息.这用于确定某人何时开始特定课程,并确保他们从第一堂课开始.这工作正常,但我遇到的问题是每个课程都在运行此查询.例如,找到每个人在特定课程中的开始日期,而不是每个课程的开始日期.现在,我只是在运行更通用的查询并在biz层中对其进行过滤.
I have a table that tracks attendance in a course. The columns are the courseid, lesson, personid, and date. I have a query (below) that extracts the earliest date a person appears along with the associated course, lesson, and personid. This is used to determine when a person started a particular course and ensure they started with the first lesson. This works fine, but where I am stuck is running this query per course. For example, finding the first date each person in a particular course started it rather than for every course. Right now I am just running the more general query and filtering it in the biz layer.
我对此感到困惑,因此请原谅任何错别字:
I obfuscated this a bit so forgive any typos:
select a.courseid,
a.lesson,
a.personid,
a.thedate
from (select personid,
min(thedate) as earliestdate
from attendance
group by personid) as x
inner join attendance as a on (a.personid = x.personid and a.thedate = x.thedate)
推荐答案
仅对person_id进行分组,当然在内部查询中:
Just group over person_id, course in the inner query:
select a.courseid, a.lesson, a.personid, a.thedate
from (
select personid, courseid, min(thedate) as earliestdate
from attendance
group by personid, courseid
) as x
inner join attendance as a
on (a.personid = x.personid and
a.thedate = x.thedate and
a.courseid=x.course_id)
这篇关于如何选择一组最小值的行,同时还使用where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!