如何减少许多相似的相关子查询? [英] How to reduce many similar correlated subqueries?

问题描述

这是一个更大的声明的一部分，但我想知道 CTE 或其他方法是否有助于使其更高效或更简洁.我可以将其编写为表值函数并将其包含在我的 from 子句中，但如果有其他解决方案，我希望避免额外的对象.

This is part of a larger statement, but I'm wondering if CTE or another method would help make this more efficient or cleaner. I could write it as a table-valued function and include it in my from clause, but I'd like to avoid extra objects if there is another solution.

SELECT TOP 1 ... 此处的子查询仅在我的费率的生效日期早于基表时捕获，但我不喜欢为每一列重复它们我需要访问.有没有更好的方法来实现这一点，或者这是一个正常的声明?

The SELECT TOP 1 ... sub-queries here simply catch when I have a rate with an earlier effective date than the base table, but I'm not fond of repeating them for each column I need to access. Is there a better way to accomplish this, or is this a normal looking statement?

SELECT j.EmployeeId
       ,j.CompanyId
       ,j.JobCode
       ,COALESCE(j.CustomWageRate, r.WageRate, (SELECT TOP 1 WageRate
                                                FROM   ContractLabor.CompanyJobRates
                                                WHERE  CompanyId = j.CompanyId
                                                       AND JobCode = j.JobCode
                                                       AND EffectiveDate < j.EffectiveDate
                                                ORDER  BY EffectiveDate DESC), 0) AS EffectiveRate
       ,COALESCE(r.CustomBurdenRateReg, (SELECT TOP 1 CustomBurdenRateReg
                                         FROM   ContractLabor.CompanyJobRates
                                         WHERE  CompanyId = j.CompanyId
                                                AND JobCode = j.JobCode
                                                AND EffectiveDate < j.EffectiveDate
                                         ORDER  BY EffectiveDate DESC)) AS CustomBurdenRateReg
       ,COALESCE(r.CustomBurdenRateOvt, (SELECT TOP 1 CustomBurdenRateOvt
                                         FROM   ContractLabor.CompanyJobRates
                                         WHERE  CompanyId = j.CompanyId
                                                AND JobCode = j.JobCode
                                                AND EffectiveDate < j.EffectiveDate
                                         ORDER  BY EffectiveDate DESC)) AS CustomBurdenRateOvt
       ,COALESCE(r.CustomBurdenRateDbl, (SELECT TOP 1 CustomBurdenRateDbl
                                         FROM   ContractLabor.CompanyJobRates
                                         WHERE  CompanyId = j.CompanyId
                                                AND JobCode = j.JobCode
                                                AND EffectiveDate < j.EffectiveDate
                                         ORDER  BY EffectiveDate DESC)) AS CustomBurdenRateDbl
       ,j.EffectiveDate
FROM   ContractLabor.EmployeeJobDetails j
       LEFT JOIN ContractLabor.CompanyJobRates r
         ON j.CompanyId = r.CompanyId
            AND j.JobCode = r.JobCode
            AND j.EffectiveDate = r.EffectiveDate

推荐答案

SELECT j.EmployeeId
       ,j.CompanyId
       ,j.JobCode
       ,COALESCE(j.CustomWageRate, r.WageRate, ca.WageRate, 0) AS EffectiveRate
       ,COALESCE(r.CustomBurdenRateReg, ca.CustomBurdenRateReg) AS CustomBurdenRateReg
       ,COALESCE(r.CustomBurdenRateOvt, ca.CustomBurdenRateOvt) AS CustomBurdenRateOvt
       ,COALESCE(r.CustomBurdenRateDbl, ca.CustomBurdenRateDbl) AS CustomBurdenRateDbl
       ,j.EffectiveDate
FROM   ContractLabor.EmployeeJobDetails j
       LEFT JOIN ContractLabor.CompanyJobRates r
         ON j.CompanyId = r.CompanyId
            AND j.JobCode = r.JobCode
            AND j.EffectiveDate = r.EffectiveDate

       OUTER APPLY --or CROSS APPLY
       (
            SELECT TOP 1 WageRate
                    ,CustomBurdenRateReg
                    ,CustomBurdenRateOvt
                    ,CustomBurdenRateDbl
            FROM   ContractLabor.CompanyJobRates
            WHERE  CompanyId = j.CompanyId
                    AND JobCode = j.JobCode
                    AND EffectiveDate < j.EffectiveDate
            ORDER  BY EffectiveDate DESC       
       ) ca  

这篇关于如何减少许多相似的相关子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！