Oracle SQL - 过去 12 个月的完整数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39794468/
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 - Last 12 full months of data
提问by Chris Sell
I need a query that will dynamically pull the last 12 full months of shipping data (excluding current month). So with today being September 30, 2016, I would need data from September 1, 2015 to August 31, 2016. Tomorrow, the query would change to the date range of 10-1-15 to 9-30-16.
我需要一个查询来动态提取最近 12 个月的运输数据（不包括当月）。因此，今天是 2016 年 9 月 30 日，我需要从 2015 年 9 月 1 日到 2016 年 8 月 31 日的数据。明天，查询将更改为 10-1-15 到 9-30-16 的日期范围。
Here is what I have currently:
这是我目前所拥有的：
WHERE (shipdate BETWEEN TRUNC(sysdate, 'Year') AND sysdate)
This query pulls data from the beginning of the calendar year up to TODAY's date instead of the previous 12 completed months. I have found answers for this with MySQL and MS SQL Server but not for Oracle. How can this be accomplished in Oracle?
此查询将数据从日历年的开始拉到今天的日期，而不是前 12 个完整的月份。我已经在 MySQL 和 MS SQL Server 上找到了答案，但在 Oracle 上找不到。这如何在 Oracle 中实现？
回答by mathguy
between add_months(trunc(sysdate, 'month'), -12) and trunc(sysdate, 'month')
If a shipment may actually be timestamped at exactly midnight and shipments with a timestamp of Sept. 1 at 00:00:00 should not be included, then "between" should be changed to
如果货件的时间戳实际上可能恰好在午夜，并且不应包括时间戳为 9 月 1 日 00:00:00 的货件，则“之间”应更改为
shipdate >= add_months(trunc(sysdate, 'month'), - 12)
and shipdate < trunc(sysdate, 'month')
回答by Aleksej
Say you have a table with a list of dates, for example last 1000 days:
假设您有一个包含日期列表的表格，例如过去 1000 天：
create table tableTest(shipDate) as
(
select trunc(sysdate) - level +1
from dual
connect by level < 1000
)
you can use the following:
您可以使用以下内容：
select min(shipdate), max(shipdate)
from tableTest
where shipDate between trunc(add_months(sysdate, -12), 'MONTH') and TRUNC(sysdate, 'MONTH') -1
