在 CASE SQL 语句中使用 BETWEEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4593355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using BETWEEN in CASE SQL statement
提问by andreas
I want to get the avarage rate for all 12 months from our rate table and divide it by months, i started writing an SQL select with case, but i seem to be doing something wrong in the "Between" part..here's my SQL
我想从我们的费率表中获得所有 12 个月的平均费率并将其除以月数，我开始编写一个带大小写的 SQL 选择，但我似乎在“之间”部分做错了......这是我的 SQL
SELECT AVG(SELL_RATE),
AVG(BUY_RATE),
CASE MONTHS
WHEN RATE_DATE( BETWEEN '2010-01-01' AND '2010-01-31') THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
FROM RATE
WHERE CURRENCY_ID = CURRENCY -033'
回答by BG100
Take out the MONTHS from your case, and remove the brackets... like this:
从你的箱子里取出 MONTHS，然后取下括号......像这样：
CASE
WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
You can think of this as being equivalent to:
你可以认为这相当于：
CASE TRUE
WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
回答by David M?rtensson
You do not specify why you think it is wrong but I can se two dangers:
你没有具体说明为什么你认为这是错误的，但我可以看到两个危险：
BETWEEN can be implemented differently in different databases sometimes it is including the border values and sometimes excluding, resulting in that 1 and 31 of january would end up NOTHING. You should test how you database does this.
BETWEEN 可以在不同的数据库中以不同的方式实现，有时包括边界值，有时不包括，导致 1 月 1 日和 31 日最终什么都没有。您应该测试您的数据库如何执行此操作。
Also, if RATE_DATE contains hours also 2010-01-31 might be translated to 2010-01-31 00:00 which also would exclude any row with an hour other that 00:00.
此外，如果 RATE_DATE 包含小时，则 2010-01-31 也可能被转换为 2010-01-31 00:00，这也将排除除 00:00 以外的小时的任何行。
