我創建了一個查詢，它根據開始日期和結束日期生成一組日期。
一切正常，但是當我嘗試過濾基礎表以僅生成我的范圍之間的記錄時，它仍然會產生所有基礎日期。
我試過在兩者之間使用，使用 to_date() 將變數更改為日期，但沒有任何效果。
SET DATE_START_RANGE = '2018-12-1';
SET DATE_END_RANGE = CASE
WHEN CURRENT_DATE = LAST_DAY(CURRENT_DATE) THEN CURRENT_DATE
ELSE LAST_DAY(DATEADD(MONTH,-1, CURRENT_DATE))
END;
SELECT DATES
--,$DATE_START_RANGE
--,$DATE_END_RANGE
FROM (
SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01') :: DATE AS DATES
FROM TABLE(GENERATOR(ROWCOUNT => 100000))
WHERE 1 =1
) LIST_OF_DATES
WHERE 1 = 1
AND DATES >= $DATE_START_RANGE -- this filter is not being picked up
AND DATES <= $DATE_END_RANGE -- this filter is not being picked up
--AND DATES BETWEEN $DATE_START_RANGE AND $DATE_END_RANGE -- this filter is not being picked up
AND DATES LIKE '____-06-%' OR DATES LIKE '____-12-%'
有任何想法嗎？
uj5u.com熱心網友回復：
該OR條件是問題AND DATES LIKE '____-06-%' OR DATES LIKE '____-12-%'：
SELECT DATES
--,$DATE_START_RANGE
--,$DATE_END_RANGE
FROM (
SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01') :: DATE AS DATES
FROM TABLE(GENERATOR(ROWCOUNT => 100000))
WHERE 1 =1
) LIST_OF_DATES
WHERE 1 = 1
AND DATES >= $DATE_START_RANGE -- this filter is not being picked up
AND DATES <= $DATE_END_RANGE -- this filter is not being picked up
AND (DATES LIKE '____-06-%' OR DATES LIKE '____-12-%')
-- here should be brackets around condition
更簡潔的方式使用LIKE ANY (..., ...)語法：
SELECT DATES
FROM (
SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01')::DATE AS DATES
FROM TABLE(GENERATOR(ROWCOUNT => 100000))
) LIST_OF_DATES
WHERE DATES BETWEEN $DATE_START_RANGE AND $DATE_END_RANGE
AND DATES LIKE ANY ('____-06-%', '____-12-%');
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346746.html
上一篇：SQL如何用兩個表連接求和
下一篇：更新MySql中的列