sql - SQL Server 2005 中的复杂串联

Question

我正在研究两个系统之间的集成。在我的类(class)系统中，有一个对讲师的引用，该讲师应该是一名工作人员。问题是类(class)系统允许在本地创建讲师记录(我已经有一份工作从我们的员工系统提供给类(class)系统)。

一门类(class)可以有不止一位讲师，出于商业原因，有时会创建本地讲师记录作为占位符。我需要将所有“真正的”讲师连接成一个字符串，但如果为类(class)设置的任何讲师不是“真正的”讲师，那么我需要输出一个空字符串。也有可能类(class)是在没有分配任何讲师的情况下创建的。

类(class)系统

Courses instructorID InstructorName  InstructorOrder
-----------------------------------------------------
ach01   1            Smith           1
ach01   2            Brown           2
phy01   3            James           1
sci01   1            Smith           1
sci01   4            Doe             2
acc01   NULL         NULL            NULL

人员系统

ID   LastName
--------------
1    Smith
2    Brown
3    James

输出

Course  Instructors
-------------------------
arc01   'Smith, Brown'
phy01   'James'
sci01   ''
acc01   ''

这是我想出的 SQL，但我想知道是否有更好的方法来获得相同的结果

select courseID,
       isnull(case max(case when x.rn = 1 then isnull(lastname, '-|-') else '' end) when '-|-' then NULL else max(case when x.rn = 1 then lastname else '' end) end +
              case max(case when x.rn = 2 then isnull(lastname, '-|-') else '' end) when '-|-' then NULL else max(case when x.rn = 2 then ', ' + lastname else '' end) end +
              case max(case when x.rn = 3 then isnull(lastname, '-|-') else '' end) when '-|-' then NULL else max(case when x.rn = 3 then ', ' + lastname else '' end) end +
              case max(case when x.rn = 4 then isnull(lastname, '-|-') else '' end) when '-|-' then NULL else max(case when x.rn = 4 then ', ' + lastname else '' end) end
              , '') Instructors
from (select courseID, s.lastname,
             ROW_NUMBER() over(partition by courseID order by InstructorOrder) rn
      from Courses c left join  
           Active_Staff s on c.instructorID = s.ID 
      ) x
group by courseID

Answer 1

这使用了一些不同的语法，但查询基本上与您已有的查询完全相同。我不认为会有任何性能差异。

select P.Courses,
       case when S.Instructors like '%NOSTAFF%' then '' else S.Instructors end as Instructors
from (
     select C.Courses,
            isnull(S.LastName, 'NOSTAFF') as LastName,
            row_number() over(partition by C.Courses order by C.InstructorOrder) as rn
     from Courses as C
       left outer join Staff as S
         on C.instructorID = S.ID
     ) as T
pivot (
      max(T.LastName) for T.rn in ([1],[2],[3],[4])
      ) as P
cross apply
      (
      select isnull(P.[1], '')+isnull(P.[2], '')+isnull(P.[3], '')+isnull(P.[4], '') as Instructors
      ) as S