[SQL] 避免重覆的 WHERE 條件

進行 TOP 10 Turning 時的改善實例，下面兩個 T-SQL 語法片段是從原語法中截取出來

TSQL1：觀察到延伸資料表［W］內已經有下 WHERE 條件［18、19］，但延伸資料表［W］又下了一次相同效果的 WHERE 語法［32、33］

-- TSQL1
SELECT 
    w.order_no , 
    w.pid, 
    W.DDATE, 
    sum(w.qty) AS qty, 
    s.modelcode, 
    s.odno, 
    s.opt_no
FROM
    (
        SELECT 
            ORDER_NO, PID, 
            QTY, 
            (right(replicate('0', 3) + cast(year(date)-1911 AS VARCHAR),3)+RIGHT(REPLICATE('0',2)+CAST(MONTH(DATE) AS VARCHAR),2)) AS DDATE,
            WK_ID
        FROM pmwkok
        WHERE (date BETWEEN '20121101' AND '20131130' ) 
            AND WK_ID= 'D1021'
    ) as w
    join pmsetorder as s on w.order_no = s.order_no
    JOIN
        (
            SELECT 
                K.order_no, 
                min(K.PID) AS PID
            FROM PMWKOK AS K
            WHERE (K.date BETWEEN '20121101'  AND '20131130' )
                AND K.WK_ID = 'D1021'
            GROUP BY K.ORDER_NO
        ) AS V ON V.ORDER_NO = W.ORDER_NO AND V.PID=W.PID
WHERE w.Ddate BETWEEN '10111' AND '10211'
    AND w.wk_id= 'D1021'
GROUP BY W.DDATE,W.order_no,W.pid,s.modelcode,s.odno,s.opt_no

TSQL2：可以觀察到延伸資料表［CS］內 UNION ALL 中的兩個子集合已經有［23、24］和［43、44］WHERE 條件，但延伸資料表［CS］又下相同 WHERE 條件［48］

-- TSQL2
SELECT 
    CS.[MONTH], 
    SUM(CS.HASPAID) AS HASPAID, 
    SUM(cs.exmoney) as exmoney, 
    SUM(CS.HASPAID)- SUM(cs.exmoney) as [normal], 
    case 
        when SUM(CS.HASPAID)=0 then 0
        ELSE ROUND(SUM(cs.exmoney)/SUM(CS.HASPAID)*100,2) 
    end [rate]
FROM
    (
        SELECT 
            MS.[MONTH], 
            CASE
                WHEN E.WK_ID='D2031'OR E.WK_ID='D2032' THEN 'D203'
                ELSE E.WK_ID
            END WK_ID, 
            sum(ROUND(MS.Salary + MS.YearMoney + MS.PostMoney + MS.TechMoney + MS.ETCMoney + MS.P_Bonus + MS.O_Salary + MS.O_Fee + MS.WorkWayFee + MS.Wextra + MS.Auxeat, 0)) AS HASPAID, 
            SUM( MS.O_Salary + MS.O_Fee) as exmoney
        FROM PRIVATE.dbo.MonthSalaryDetail AS MS
            JOIN PRIVATE.dbo.Employ AS E ON MS.EmpNO = E.EMPNO
        WHERE (MS.[Month] BETWEEN '10111'  AND '10211' )
            AND E.WK_ID LIKE 'D1021%'
        GROUP BY E.WK_ID , MS.[MONTH]
        UNION ALL
        SELECT 
            G.[MONTH],
            CASE
                WHEN G.WK_ID='D2031'OR G.WK_ID='D2032' THEN 'D203'
                ELSE G.WK_ID
            END WK_ID, 
            0 as haspaid, 
            sum(HASPAID) AS exmoney
        FROM
            (
                SELECT 
                    E.WK_ID, 
                    (right(replicate('0', 3) + cast(year(H.DATE) - 1911 AS VARCHAR), 3) + right(REPLICATE('0', 2) + cast(month(H.DATE) AS VARCHAR), 2)) AS [MONTH], 
                    H.Salary AS HASPAID
                FROM PRIVATE.dbo.HolidaySalary AS H
                    JOIN PRIVATE.dbo.Employ AS E ON H.EmpNO = E.EMPNO
                WHERE (H.DATE BETWEEN '20121101' AND '20131130')
                    AND E.WK_ID  LIKE 'D1021%'
            ) AS G
        GROUP BY G.WK_ID, G.[MONTH]
    ) AS CS
where cs.wk_id= 'D1021'
GROUP BY CS.MONTH

只拿掉［32、33］、［48］這兩個多餘的 WHERE 條件來進行改善，下面是改善前後的比較

Logical Read 比較表（只列出有變化的 Table）

Table 名稱	修改前	修改後
PMWKOK	242,910	9,689
PMSetOrder	36,026	10,972
MonthSalaryDetail	3,473	714

CPU Time 比較表

	修改前	修改後
CPU Time (ms)	344	171

這不知道是第 N 次發現同事下重覆 WHERE 條件，原是希望藉此來限制資料量，但沒想到此舉也造成反效果