Oracle SQL 上个月查询问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4287103/
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
Oracle SQL Previous Month query issue
提问by Coffeee
So far I've got the following:
到目前为止，我有以下几点：
SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
TO_CHAR(sysdate, 'mm')-1 as "Current_Month"
FROM "HOL_DEPART_DATES" "HOL_DEPART_DATES"
WHERE "Depart_Month" = "Current_Month"
However this gives me an error:
但是，这给了我一个错误：
ORA-00904: "Current_Month": invalid identifier
ORA-00904: "Current_Month": 无效标识符
However without the WHERE clause, it works fine. Any ideas?
但是没有 WHERE 子句，它工作正常。有任何想法吗？
回答by Tony Andrews
Unfortunately you cannot reference the column aliases in the WHERE clause as they are not yet available. You can either do this:
不幸的是，您不能在 WHERE 子句中引用列别名，因为它们尚不可用。你可以这样做：
select TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
TO_CHAR(sysdate, 'mm')-1 as "Current_Month"
from "HOL_DEPART_DATES" "HOL_DEPART_DATES"
where TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') = TO_CHAR(sysdate, 'mm')-1
or do this:
或这样做：
select "Depart_Month", "Current_Month"
from
( select TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
TO_CHAR(sysdate, 'mm')-1 as "Current_Month"
from "HOL_DEPART_DATES" "HOL_DEPART_DATES"
)
where "Depart_Month" = "Current_Month"
回答by Vincent Malgrat
the SELECT clause is evaluated after the WHERE clause in SQL. This is why the WHERE clause can't see the aliases you have defined.
SELECT 子句在 SQL 中的 WHERE 子句之后计算。这就是 WHERE 子句看不到您定义的别名的原因。
Either:
任何一个：
run a subquery:
SELECT "Depart_Month", "Current_Month" FROM (SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') AS "Depart_Month", TO_CHAR(SYSDATE, 'mm') - 1 AS "Current_Month" FROM "HOL_DEPART_DATES" "HOL_DEPART_DATES") WHERE "Depart_Month" = "Current_Month"or use the expression in the where clause:
SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') AS "Depart_Month", TO_CHAR(SYSDATE, 'mm') - 1 AS "Current_Month" FROM "HOL_DEPART_DATES" "HOL_DEPART_DATES" WHERE TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') = TO_CHAR(SYSDATE, 'mm') - 1
运行子查询：
SELECT "Depart_Month", "Current_Month" FROM (SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') AS "Depart_Month", TO_CHAR(SYSDATE, 'mm') - 1 AS "Current_Month" FROM "HOL_DEPART_DATES" "HOL_DEPART_DATES") WHERE "Depart_Month" = "Current_Month"或使用 where 子句中的表达式：
SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') AS "Depart_Month", TO_CHAR(SYSDATE, 'mm') - 1 AS "Current_Month" FROM "HOL_DEPART_DATES" "HOL_DEPART_DATES" WHERE TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') = TO_CHAR(SYSDATE, 'mm') - 1
回答by Ronnis
I would stay away from performing arithmtic on the return value from TO_CHAR. When subtracting 1 from the string '01' (januari) we won't end up with 12 (december).
我不会对 TO_CHAR 的返回值执行算术运算。当从字符串 '01' (januari) 中减去 1 时，我们不会得到 12（十二月）。
You should do something like this:
你应该做这样的事情：
select *
from hol_depart_dates
where depart_date between trunc(add_months(sysdate, -1), 'MM')
and trunc(sysdate, 'MM') - interval '1' second;
Now the query can use an index on depart_date. And TO_CHAR does not have to be called for every row.
现在，查询可以使用 depart_date 上的索引。并且不必为每一行调用 TO_CHAR。
回答by Jeffrey Kemp
If you want to compare dates, you should not convert them to strings - Oracle has builtin support for date/time arithmetic.
如果要比较日期，则不应将它们转换为字符串 - Oracle 内置了对日期/时间算术的支持。
In your case, it seems you're querying the table where the month of the departure date is equal to the month previous - which doesn't make sense. If it is currently November, then the query would return rows from October 2010, October 2009, October 2008, etc. Are you sure that's what you wanted?
在您的情况下，您似乎正在查询出发日期的月份等于上个月的表格 - 这没有意义。如果当前是 11 月，那么查询将返回 2010 年 10 月、2009 年 10 月、2008 年 10 月等的行。您确定这是您想要的吗？
One of the best ways to use date arithmetic to determine if a date is within the previous month is to use a combination of TRUNC(date,'MONTH'), which returns the first day of the current month, with ADD_MONTHS(date,-1), which gets the date one month prior.
使用日期算术确定日期是否在上个月内的最佳方法之一是使用 TRUNC(date,'MONTH') 的组合，它返回当月的第一天，与 ADD_MONTHS(date,- 1)，获取一个月前的日期。
SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
TO_CHAR(ADD_MONTHS(sysdate, -1), 'mm') as "Current_Month"
FROM "HOL_DEPART_DATES"
WHERE "HOL_DEPART_DATES"."DEPART_DATE"
BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-1)
AND TRUNC(SYSDATE,'MONTH') - 0.00001;
The "0.00001" subtracts one second from the date, so the date range effectively becomes (assuming it is now November 2010) 01-Oct-2010 00:00:00 to 31-Oct-2010 23:59:59.
“0.00001”从日期中减去一秒，因此日期范围实际上变为（假设现在是 2010 年 11 月）01-Oct-2010 00:00:00 到 31-Oct-2010 23:59:59。
An alternative, equivalent syntax would be:
另一种等效的语法是：
SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
TO_CHAR(ADD_MONTHS(sysdate, -1), 'mm') as "Current_Month"
FROM "HOL_DEPART_DATES"
WHERE "HOL_DEPART_DATES"."DEPART_DATE"
>= ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-1)
AND "HOL_DEPART_DATES"."DEPART_DATE" < TRUNC(SYSDATE,'MONTH');
