Oracle-获取过去一年的数据(动态) [英] Oracle - Get data from past year (Dynamic)

问题描述

我对从数据库中获取过去一年需要获取的数据有一些疑问(动态的，不是硬编码的)

i have some question regarding about get data from database which need to fetch from past year ( dynamic , not hard-coded )

表:BookingTable

Table: BookingTable

bookingID   userID     Timebooked 
1             0003    10-May-2016
2             0001    10-May-2018
3             0001    10-Apr-2017
4             0001    10-Apr-2017
5             0003    10-Jan-2011
6             0006    10-Apr-2018
7             0003    10-Apr-2016
8             0006    10-Apr-2015
9             0001    10-Apr-2017

这是我的下面的代码

SELECT userID, COUNT(userID) AS count
FROM BookingTable 
GROUP BY userID
HAVING COUNT(userID) >2

这是我上面查询的结果

userID  count
0001      4
0003      3

在表中，我想添加从过去一年到当前日期的时间表.

From the table i would like to add where the timebooked is from past year to current date.

推荐答案

您可以使用sysdate - interval '1' year表示一年前的今天:

You may use sysdate - interval '1' year to represent today's date one year ago:

SELECT userID, COUNT(userID) AS count
FROM BookingTable 
WHERE Timebooked BETWEEN SYSDATE - INTERVAL '1' year AND SYSDATE
GROUP BY userID
HAVING COUNT(userID) > 2

正如@Wernfried在下面正确指出的那样，以上内容将在a年的2月29日失败.相反，请使用以下解决方法:

As @Wernfried correctly pointed out below, the above would fail for February 29 in a leap year. Instead, use this workaround:

WHERE Timebooked BETWEEN ADD_MONTHS(SYSDATE, -12) AND SYSDATE

这篇关于Oracle-获取过去一年的数据(动态)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！