TSQL查询优化咨询：无需嵌套实现员工任职时长分位数匹配

你要的无嵌套更简洁的实现可以通过OUTER APPLY + TOP 1的逻辑完成，代码更易读，执行效率也优于你现有两种嵌套方案，尤其适合批量编写同类查询的场景。

完整代码 #

WITH quintiles AS (
    SELECT 'A' as costcenter 
    ,'Q1' as quintile
    ,8 as quintilevalue
    UNION SELECT 'A','Q2',12
    UNION SELECT 'A','Q3',14
    UNION SELECT 'A','Q4',18
    UNION SELECT 'A','Max',50
    UNION SELECT 'B','Q1',5
    UNION SELECT 'B','Q2',10
    UNION SELECT 'B','Q3',12
    UNION SELECT 'B','Q4',16
    UNION SELECT 'B','Max',45
),
employees AS
(
    SELECT 'A' as costcenter 
    ,'W' as employee
    ,'2021-01-01' as hiredate
    UNION SELECT 'A','X','2021-02-08'
    UNION SELECT 'B','Y','2020-12-16'
    UNION SELECT 'B','Z','2021-01-15'
),
workcomplete AS
(
    SELECT 'W' as employee
    ,'2021-02-26' as workdate
    ,4 as widgetsassembled
    UNION SELECT 'W','2021-03-05',5
    UNION SELECT 'X','2021-05-24',6
    UNION SELECT 'X','2021-05-31',3
    UNION SELECT 'Y','2021-04-07',2
    UNION SELECT 'Y','2021-04-14',8
    UNION SELECT 'Z','2021-02-07',4
    UNION SELECT 'Z','2021-02-14',1
)
-- 核心查询逻辑（无嵌套）
SELECT 
    wc.employee 员工,
    wc.workdate 工作日期,
    wc.widgetsassembled 组装部件数,
    e.costcenter 成本中心,
    DATEDIFF(week,e.hiredate,wc.workdate) 任职时长,
    q.quintilevalue 最小匹配分位值,
    q.quintile 分位
FROM workcomplete wc 
LEFT JOIN employees e ON wc.employee = e.employee
OUTER APPLY (
    SELECT TOP 1 quintilevalue, quintile
    FROM quintiles q
    WHERE q.costcenter = e.costcenter 
    AND q.quintilevalue >= DATEDIFF(week,e.hiredate,wc.workdate)
    ORDER BY q.quintilevalue ASC
) q

方案优势 #

• 完全没有嵌套查询，逻辑直白，只需要关联基础表后直接匹配符合条件的最小分位值，非常容易理解和维护
• 执行效率更高：避免了原方案中先全量关联所有符合条件的分位、再聚合计算最小值、最后二次关联分位表的冗余操作，数据量越大性能优势越明显
• 代码精简，你后续写同类查询只需要修改基础表关联和OUTER APPLY里的匹配条件即可，开发效率更高

实际业务使用时，直接删掉CTE里的测试数据，换成你实际的表名调用核心查询部分即可。

内容的提问来源于stack exchange，提问作者Tristen Hannah