我有以下查詢，它根據某些條件選擇 AAD_00TO30 列的總和。
當我洗掉以下條件時，查詢將在 1 秒內執行，但包含相同條件時需要一分鐘以上。
有人可以建議我任何替代方法來修改查詢以獲得更好的性能。
AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE)
FROM MST_AR_AS_ON_DATE B
WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE))
詢問：
SELECT '00-30 #66ff66',SUM(A.AAD_00TO30) FROM MST_AR_AS_ON_DATE A
WHERE MONTH(A.AAD_DATE) = MONTH(DATEADD(MM,-1,GETDATE()))
AND YEAR(A.AAD_DATE) = YEAR(DATEADD(MM,-1,GETDATE()))
AND A.AAD_RESP_NOW = 4
AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE)
FROM MST_AR_AS_ON_DATE B
WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE))
uj5u.com熱心網友回復：
嘗試使用RANK()來標記符合具有該月最后日期的條件的行。然后消除沒有獲勝排名的行：
WITH
MST_AR_AS_ON_DATE_RANKED AS (
SELECT
*,
RANK() OVER (
PARTITION BY
YEAR(AAD_DATE),
MONTH(AAD_DATE)
ORDER BY
AAD_DATE DESC -- last day of month ranked highest
) AS AAD_DATE_RANK
FROM
MST_AR_AS_ON_DATE
)
SELECT
'00-30 #66ff66',
SUM(AAD_00TO30)
FROM
MST_AR_AS_ON_DATE_RANKED
WHERE
MONTH(AAD_DATE) = MONTH(DATEADD(MM,-1,GETDATE()))
AND YEAR(AAD_DATE) = YEAR(DATEADD(MM,-1,GETDATE()))
AND AAD_RESP_NOW = 4
AND AAD_DATE_RANK = 1
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/514364.html
標籤：sql表现qsql查询
上一篇：如何加快包含HAVING的查詢？
下一篇：查詢改進-gremlin#2