Server 2005中的复杂级联

Question

我正在研究两种系统的集成。在我的课程体系中，有一个指导员，应该是一名工作人员。问题是课程系统允许在本地创建讲师记录(我已经有了一份工作，从我们的员工系统到课程系统)。

一门课程可以有一名以上的讲师，而且出于商业原因，本地讲师的记录有时会被创建为场所持有者。我需要将所有“真实”的教员串联成一个字符串，但是如果为课程设置的任何教员不是“真实的”讲师，那么我需要输出一个空字符串。这门课程也有可能是在没有指定任何讲师的情况下创建的。

课程系统

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

Answer 2

这看起来有点复杂(也许确实如此)，但它完成了任务：

;WITH CTE1 AS 
(
    SELECT * FROM Courses c
    LEFT JOIN dbo.Active_Staff s ON c.instructorID = s.ID
)
,CTE2 AS
(  
    SELECT  CourseID, 
        STUFF((SELECT ', ' + LastName 
              FROM   CTE1 c2 
              WHERE  c2.CourseID = c1.CourseID 
              ORDER BY c2.InstructorOrder
              FOR XML PATH('')), 1, 2, '')  LastNames
    FROM  CTE1 c1 
    GROUP BY CourseID
)
SELECT
    CourseID,
    CASE WHEN EXISTS (SELECT * FROM CTE1  WHERE LastName IS NULL AND CTE1.CourseID = Cte2.CourseID) THEN '' ELSE LastNames END AS Instructors
FROM CTE2 

基本上，我们首先将所有的字符串串联起来--使用一些东西和XML路径组合，然后用空字符串替换那些至少有一个“虚拟”指导员的字符串。

这是演示