SQL Server 子查询循环

Question

我有一个子查询，用于汇总列表事件的天数。我们要分别分析这些列表的 3 个不同优先级。子查询工作正常，但我必须重复自己 3 次，我忍不住觉得有人可以为我指明一个更优雅的方向。

我正在寻找类似的东西

Declare @Priorities text = ['H','M','L']

Foreach(priority in priorities)
  (SELECT SUM(
        CASE
            WHEN(jlh_inner.StartDate IS NULL) THEN 0
            WHEN(jlh_inner.EndDate IS NULL) THEN
                DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
            ELSE
                DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
        END)
    FROM ListingHistory jlh_inner
    WHERE jlh_inner.JobTitleId = jlh.JobTitleId
    AND jlh_inner.OfficeCode = rof.code
    AND jlh_inner.Priority = priority)

现在的完整代码是:

BEGIN
SELECT rof.location AS location,
    jlh.TitleId AS TitleId,
    jt.Title AS Title,
    (SELECT SUM(
        CASE
            WHEN(jlh_inner.StartDate IS NULL) THEN 0
            WHEN(jlh_inner.EndDate IS NULL) THEN
                DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
            ELSE
                DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
        END)
    FROM ListingHistory jlh_inner
    WHERE jlh_inner.JobTitleId = jlh.JobTitleId
    AND jlh_inner.OfficeCode = rof.code
    AND jlh_inner.Priority = 'H') AS HighPriorityDays,
    (SELECT SUM(
        CASE
            WHEN(jlh_inner.StartDate IS NULL) THEN 0
            WHEN(jlh_inner.EndDate IS NULL) THEN
                DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
            ELSE
                DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
        END)
    FROM ListingHistory jlh_inner
    WHERE jlh_inner.JobTitleId = jlh.JobTitleId
    AND jlh_inner.OfficeCode = rof.code
    AND jlh_inner.Priority = 'M') AS MediumPriorityDays,
    (SELECT SUM(
        CASE
            WHEN(jlh_inner.StartDate IS NULL) THEN 0
            WHEN(jlh_inner.EndDate IS NULL) THEN
                DATEDIFF(dd, jlh_inner.StartDate, GETDATE())
            ELSE
                DATEDIFF(dd, jlh_inner.StartDate, jlh_inner.EndDate)
        END)
    FROM ListingHistory jlh_inner
    WHERE jlh_inner.JobTitleId = jlh.JobTitleId
    AND jlh_inner.OfficeCode = rof.code
    AND jlh_inner.Priority = 'L') AS LowPriorityDays

FROM Offices rof,
    ListingHistory jlh,
    JobTitle jt

WHERE rof.code = jlh.OfficeCode
AND jt.JobTitleID = jlh.JobTitleId

GROUP BY rof.location, rof.code, jlh.TitleId, jt.Title

ORDER BY location

结束

Answer 1

您有一些非常糟糕的编码习惯，您需要完全停止使用它们，相关子查询就是其中之一。它们是性能 killer ，没有理由使用它们。此外，您应该开始使用显式连接，尤其是因为您使用的是 SQL Server，并且外连接的隐式连接语法不仅已被弃用，而且实际上甚至回到 SQL Server 2000 时也被破坏，并且组合隐式内部和显式连接通常会导致不正确的结果和因此隐式连接更难维护，并且更有可能发生意外交叉连接，当然，已经过时了将近 20 年。

查看这段代码是否满足您的需求:

SELECT rof.location AS location,     
jlh.TitleId AS TitleId,     
jt.Title AS Title,     
SUM(CASE WHEN(jlh.StartDate IS NULL and Priority <> 'H') THEN 0  
    WHEN(jlh.EndDate IS NULL AND Priority = 'H') 
    THEN DATEDIFF(dd, jlh.StartDate, GETDATE())             
    WHEN (jlh.EndDate IS NOT NULL AND Priority = 'H') THEN DATEDIFF(dd, jlh.StartDate, jlh.EndDate) END) AS HighPriorityDays,     
SUM(CASE WHEN(jlh.StartDate IS NULL and Priority <> 'M') THEN 0  
    WHEN(jlh.EndDate IS NULL AND Priority = 'M') 
    THEN DATEDIFF(dd, jlh.StartDate, GETDATE())             
    WHEN (jlh.EndDate IS NOT NULL AND Priority = 'M') THEN DATEDIFF(dd, jlh.StartDate, jlh.EndDate) END) AS MediumPriorityDays,
SUM(CASE WHEN(jlh.StartDate IS NULL and Priority <> 'L') THEN 0  
    WHEN(jlh.EndDate IS NULL AND Priority = 'L') 
    THEN DATEDIFF(dd, jlh.StartDate, GETDATE())             
    WHEN (jlh.EndDate IS NOT NULL AND Priority = 'L') THEN DATEDIFF(dd, jlh.StartDate, jlh.EndDate) END) AS LowPriorityDays  
FROM Offices rof 
JOIN    ListingHistory jlh 
    ON rof.code = jlh.OfficeCode
JOIN     JobTitle jt  
    ON jt.JobTitleID = jlh.JobTitleId  
GROUP BY rof.location, rof.code, jlh.TitleId, jt.Title  
ORDER BY location