减少 SQL 查询中的重复结果

JH86 发表于 Dev

jh86

    select
rtrim(p.firstname) + ' ' + p.lastname as [instructor name], 
lesson_type.lessonname, 
location.locationname, 
lesson_1.lessondate
from person as p inner join lesson as l 
on p.personid = l.employeeid 
inner join location 
on l.locationid = location.locationid 
inner join lesson as lesson_1 
on p.personid = lesson_1.employeeid and location.locationid = lesson_1.locationid 
inner join lesson_type 
on l.lessontypeid = lesson_type.lessontypeid and lesson_1.lessontypeid = lesson_type.lessontypeid
order by [instructor name] ASC

我如何才能没有相同地点名称和课程名称的重复课程日期？这是我的输出：

instructor name                 lessonname      locationname    lessondate
------------------------------- --------------- --------------- ----------
Anna Dillinger                  Diamond         Brighton Ski    2014-01-12
Anna Dillinger                  Newbie          Steamboat       2014-01-01
Anna Dillinger                  Intermediate    Steamboat       2014-01-01
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Anna Dillinger                  Newbie          Mt. Holly       2014-02-12
Jeff Gregory                    Cross Country   Snow Snake      2013-12-23
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Advanced        Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12
Jeff Gregory                    Intermediate    Pine Knob       2013-12-12

保罗麦克斯韦

删除重新加入的课程_1 可能会有所帮助

SELECT
      RTRIM(p.firstname) + ' ' + p.lastname AS [instructor name]
    , lesson_type.lessonname
    , location.locationname
    , l.lessondate
FROM person AS p
INNER JOIN lesson AS l ON p.personid = l.employeeid
INNER JOIN location ON l.locationid = location.locationid
INNER JOIN lesson_type ON l.lessontypeid = lesson_type.lessontypeid
ORDER BY
      [instructor name] ASC

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。