我有一张桌子,可以跟踪课程的出勤情况。列是课程编号,课程,人员编号和日期。我有一个查询(如下),该查询提取一个人出现的最早日期以及相关的课程,课程和人格信息。这用于确定某人何时开始特定课程,并确保他们从第一堂课开始。这工作正常,但我遇到的问题是每个课程都运行此查询。例如,找到每个人在特定课程中的开始日期,而不是每个课程的开始日期。现在,我只是在运行更通用的查询,并在biz层中对其进行过滤。
我对此感到困惑,因此请原谅任何错别字:
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进行分组,当然可以在内部查询中进行:
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)