我有一個簡單的問題，我不知道我哪里做錯了。我真的很感謝你的幫助。提前感謝您的任何提示。
我有資料，需要將發布日期拆分為 1 個月、2-3 個月、4-5 個月、6-7 個月、7-9 個月等。所以，我創建了一個查詢；
Select case
when release_date >= current_date - interval '1 month' then 'Within 1 month'
when release_date between (current_date - interval '2 months') and (current_date - interval '3 months') then '2-3 months'
when release_date between (current_date - interval '4 months') and (current_date - interval '5 months') then '4-5 months'
when release_date between (current_date - interval '6 months') and (current_date - interval '7 months') then '6-7 months'
when release_date between (current_date - interval '8 months') and (current_date - interval '9 months') then '8-9 months'
when release_date between (current_date - interval '10 months') and (current_date - interval '12 months') then '9-12 months'
when release_date < current_date - interval '12 months' then '> 12 month'
end as release_date,
country,
....
最后我只能看到1 個月內，NULL和> 12 個月。
uj5u.com熱心網友回復：
我認為您的輸出正在生成“月”以外的計算。答案取決于您使用的 dbms。這是在 postgres 中執行的一種方法，但如果您使用另一個 dbms，那么您應該能夠輕松更改 CTE 中計算月份的語法。使用 CTE 可以讓您擁有干凈的 case 陳述句。
with calculated_months as
(
select release_date,
extract(year from age(current_date, release_date)) * 12
extract(month from age(current_date, release_date))
1 as months_from_current
from t1
)
select t.release_date,
case
when c.months_from_current < 2 then 'Within 1 month'
when c.months_from_current between 2 and 3 then '2-3 months'
when c.months_from_current between 4 and 5 then '4-5 months'
when c.months_from_current between 6 and 7 then '6-7 months'
when c.months_from_current between 8 and 9 then '8-9 months'
when c.months_from_current between 10 and 12 then '10-12 months'
when c.months_from_current > 12 then '12 months'
end months_since_release,
t.country
from t1 t
join calculated_months c
on c.release_date = t.release_date
資料庫小提琴在這里
編輯：不確定您的資料如何顯示，但您可能希望在 CTE 中添加“不同”（選擇不同的發布日期...）。
uj5u.com熱心網友回復：
如果我正確閱讀您的查詢，您需要交換“之間”陳述句中的值：
Select case
when release_date >= current_date - interval '1 month' then 'Within 1 month'
when release_date between (current_date - interval '3 months') and (current_date - interval '2 months') then '2-3 months'
when release_date between (current_date - interval '5 months') and (current_date - interval '4 months') then '4-5 months'
when release_date between (current_date - interval '7 months') and (current_date - interval '6 months') then '6-7 months'
when release_date between (current_date - interval '9 months') and (current_date - interval '8 months') then '8-9 months'
when release_date between (current_date - interval '12 months') and (current_date - interval '9 months') then '9-12 months'
when release_date < current_date - interval '12 months' then '> 12 month'
end as release_date,
country,
我沒有一個系統可以運行它來確認，但它應該可以作業。
問題是如何between作業，BETWEEN (lower_limit) AND (upper_limit).
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/362348.html