Oracle SQL：获取最近八个月的数据（带有 YEAR 和 MONTH 列）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7019197/
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: Get the last eight months of data (with YEAR and MONTH columns)
提问by Muhd
I have a table that has columns YEAR and MONTH, which are varchars, which have a format like MONTH = '02', YEAR = '2011'.
我有一个包含 YEAR 和 MONTH 列的表，它们是 varchars，其格式类似于 MONTH = '02', YEAR = '2011'。
What query can I use to get the last eight months of data, excluding the current month?
我可以使用什么查询来获取最近八个月的数据，不包括当月？
回答by beny23
Try
尝试
where to_date(year || month, 'YYYYMM')
between add_months(trunc(sysdate, 'MM'), -8) and trunc(sysdate)
回答by Chandu
Try this:
尝试这个：
SELECT *
FROM myTable
WHERE (year, month) IN
(
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -level), 'RRRR') AS year,
TO_CHAR(ADD_MONTHS(SYSDATE, -level), 'MM') AS month
FROM dual
CONNECT BY LEVEL < 8
)
Another version:
另一个版本：
SELECT *
FROM myTable
WHERE year||month IN
(
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -level), 'RRRRMM') AS yearmonth
FROM dual
CONNECT BY LEVEL < 8
)
回答by Andrew Lee
Best I can think of is this:
我能想到的最好的是：
SELECT * FROM table WHERE (MONTH || YEAR) IN ('012011','122010', et cetera)
It is a brute force method, so it is not feasible if you want your query to generalize.
这是一种蛮力方法，因此如果您希望查询泛化，这是不可行的。
