SQL問題診斷

問題診斷使用者反應有一個員工不能設定他的考勤月曆了。補充說明，我們員工上班都是按週一到週五上班，朝8晚5下班，但也有上三天晚班休息兩天的上班制度，所以要設定
員工的上班制度。而現在不能設定這個員工的上班制度了，那麼這個員工也沒法提交加班申請等操作了。而且快到工資結算的時候，如果不能及時提交考勤和加班等資訊，就會影響
最後的工資發放。要及時處理這個問題，只好和開發人員一起查詢正式系統上的日誌。還好和開發人員一起梳理邏輯，很快就找到了可能有問題的SQL. 但此SQL執行正常不知道為什麼會查詢出
不需要的員工資訊，這是查詢出了這個員工不符合要求，所以系統才刪除了這條不符合邏輯的資料。
這是DBA介入除錯此SQL，查詢錯誤查詢的原因。發現相關表中存在的資料如下
SELECT APPNT_DATE
       ,APPNT_CD
       ,APPNT_SEQ
       ,ACTI_STTS_CD
FROM    MEMP_AP_APPNT_DET
WHERE   ten_id='T01'
AND SITE_ID='EZ00'
AND EMP_ID = '2F4EE7C0599E11E0B358975729707EA8'
AND APPNT_DATE = '20180301';

APPNT_DATE APPNT_CD APPNT_SEQ ACTI_STTS_CD
20180301     AB         10         AA
20180301     DC         9          DA
20180301     YB         8          AA
而程式會對這個表的APPNT_SEQ值求最大值，以此找出這天裡員工的最後的狀態值
但SQL中的查詢條件是這樣的

AND R.APPNT_DATE||R.APPNT_SEQ = (SELECT MAX(X1.APPNT_DATE||X1.APPNT_SEQ)
                                  FROM MEMP_AP_APPNT_DET X1
                                 WHERE X1.TEN_ID = R.TEN_ID
                                   AND X1.SITE_ID = R.SITE_ID
                                   AND X1.EMP_ID = R.EMP_ID
                                   AND X1.APPNT_DATE <= '20180329')

一看到|| 還做比較就感覺要出事，趕緊把這段SQL拿到生產環境執行了一把。查詢出的資料中有不想要的資料。於是在 R.APPNT_DATE||R.APPNT_SEQ前面加TO_NUMBER()
再執行不想要的資料不見了。這說明 R.APPNT_DATE||R.APPNT_SEQ拼出的結果是字元型別，這種字元型別比較後和數字型別比較是有區別的
比如201803019 > 2018030110 所以最大結果是201803019 , 而不是2018030110

最終修改後的SQL

                                SELECT N.TEN_ID
                                     , ...
                                  FROM TTNA_TBS_PERSN_MAS N
                                     , MEMP_AP_APPNT_DET R -- CUR
                                     , MEMP_AP_APPNT_DET S -- PAST
                                 WHERE N.TEN_ID = 'T01'
                                   AND N.SITE_ID = 'EZ00'
                                   AND N.EMP_ID = N.EMP_ID--NVL('null', N.EMP_ID)
                                   AND N.TEN_ID = R.TEN_ID
                                   AND N.SITE_ID = R.SITE_ID
                                   AND N.EMP_ID = R.EMP_ID
                                   AND R.APPNT_DATE BETWEEN TO_CHAR(TO_DATE('20180329','YYYYMMDD')-100,'YYYYMMDD') AND '20180329'
                                   ...
                                   AND TO_NUMBER(R.APPNT_DATE||R.APPNT_SEQ) = (SELECT MAX(TO_NUMBER(X1.APPNT_DATE||X1.APPNT_SEQ)) --update
                                                                      FROM MEMP_AP_APPNT_DET X1
                                                                     WHERE X1.TEN_ID = R.TEN_ID
                                                                       AND X1.SITE_ID = R.SITE_ID
                                                                       AND X1.EMP_ID = R.EMP_ID
                                                                       AND X1.APPNT_DATE <= '20180329')
                                   AND TO_NUMBER(R.APPNT_DATE||R.APPNT_SEQ) > TO_NUMBER(S.APPNT_DATE||S.APPNT_SEQ)       --update
                                   AND SUBSTR(NVL(R.ACTI_STTS_CD, ' '), 1, 1) <> SUBSTR(NVL(S.ACTI_STTS_CD, ' '), 1, 1) -- ?? ?? ??
                                   AND TO_NUMBER(S.APPNT_DATE||S.APPNT_SEQ) = (SELECT MAX(TO_NUMBER(X2.APPNT_DATE||X2.APPNT_SEQ)) --update
                                                                      FROM MEMP_AP_APPNT_DET X2
                                                                     WHERE X2.TEN_ID = S.TEN_ID
                                                                       AND X2.SITE_ID = S.SITE_ID
                                                                       AND X2.EMP_ID = S.EMP_ID
                                                                       AND X2.APPNT_DATE < R.APPNT_DATE)
                                  ...
總結比較過程中字串的比較和數字型別的比較是有很大區別的。這裡不用過多的檢視業務的邏輯，畢竟生產環境下的SQL 執行有一定保證，除非在某些特殊情況有一定問題，那麼我們關注的是問題的細節，這裡就是技術的細節。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-2152616/，如需轉載，請註明出處，否則將追究法律責任。

SQL問題診斷

相關文章