撰寫TSQL查詢的最佳方式

我很好奇是否有更好的方法來撰寫這個查詢，無論是在語法上使更簡單/更容易遵循，還是為了處理速度。

我最近一直在寫很多類似的查詢，希望能對此進行簡化。我不是貿易開發人員，但我必須深入研究 sql

五分位數表

員工表

作業完成表

我的目標：對于 workcomplete 表中的每條記錄，找出員工完成作業時的任期是多少，以及它屬于哪個五分之一。

這就是我所做的，它作業正常：

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 t.*
    ,q.quintile
FROM (
    SELECT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure 
        ,MIN(q.quintilevalue) as smallestquintile
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue
    GROUP BY wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate)
)t
LEFT JOIN quintiles q
    ON t.smallestquintile = q.quintilevalue and t.costcenter = q.costcenter

這也有效。

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 t.*
    ,q.quintile
FROM (
    SELECT DISTINCT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure 
        ,MIN(q.quintilevalue) OVER (PARTITION BY wc.employee, wc.workdate) as smallestquintile
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue

)t
LEFT JOIN quintiles q
    ON t.smallestquintile = q.quintilevalue and t.costcenter = q.costcenter

有沒有更簡單的方法來做到這一點，而無需嵌套選擇？

uj5u.com熱心網友回復：

聽起來您只需要一個top-1-per-group查詢。

標準的解決方案是使用 ROW_NUMBER

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 t.*
FROM (
    SELECT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure
        ,q.quintile
        ,ROW_NUMBER() OVER (PARTITION BY wc.employee, wc.workdate ORDER BY q.quintilevalue) as rn
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue
)t
WHERE rn = 1;

資料庫<>小提琴

標籤：sql-server

上一篇：優化長查詢以查找帶有外鍵和表的資料庫模式

下一篇：如何使用NodeJSHttp/Https模塊獲取URL引數