oracle 在日期字段中获取特定年份的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6136615/
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
Getting data from particular year in date field
提问by Sara
Suppose i am having data as shown below
假设我有如下所示的数据
ACT_NO DATE1 AMOUNT
12111201 2/1/2009 66600.00
12111201 8/31/2009 66600.00
12111201 7/29/2008 133200.00
12111201 5/19/2009 66600.00
I need to get the sum of amount for particular year 2009 ie from 1/31/2009 to 31/12/2009. How I will get as per year? The date1 field is in date format in oracle. I am using pl sql developer
我需要获得 2009 年特定年份的金额总和，即从 1/31/2009 到 31/12/2009。我将如何获得每年？date1 字段在oracle 中是日期格式。我正在使用 pl sql 开发人员
回答by Tony Andrews
As long as date1 has no time component you could use:
只要 date1 没有时间组件，您就可以使用：
where date1 between date '2009-01-01' and '2009-12-31'
That can use an index on date1. Another solution (that could not use a simple index on date1) is:
那可以使用 date1 上的索引。另一个解决方案（不能在 date1 上使用简单索引）是：
where trunc(date1,'YYYY') = date '2009-01-01'
回答by APC
This query sums the AMT column for a given year. The inclusion of the YEAR in the result is optional; if you don't want it you won't need the GROUP BY clause either.
此查询对给定年份的 AMT 列求和。在结果中包含 YEAR 是可选的；如果你不想要它，你也不需要 GROUP BY 子句。
select to_char(date1, 'YYYY') as year
, sum(amount) as year_total
from your_table
where date1 between to_date('01-JAN-2009', 'DD-MON-YYYY')
and to_date('31-DEC-2009 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
group by to_char(date1, 'YYYY')
/
The query presumes that DATE1 has a DATE datatype and includes a time element (hence the additional formatting in the AND arm of the BETWEEN clause). If DATE1 is ill-mannered enough to be a string, the easiest kludge is just to case it to a date first.
该查询假定 DATE1 具有 DATE 数据类型并包含一个时间元素（因此在 BETWEEN 子句的 AND 分支中具有附加格式）。如果 DATE1 不够礼貌以至于成为一个字符串，那么最简单的方法就是先将它设置为日期。
回答by Harrison
with sampleData as(
select 12111201 ACT_NO, to_date('2/1/2009' ,'mm/dd/rrrr') DATE1 , 66600.00 AMOUNT from dual union all
select 12111201 ACT_NO, to_date('8/31/2009','mm/dd/rrrr') DATE1 , 66600.00 AMOUNT from dual union all
select 12111201 ACT_NO, to_date('7/29/2008','mm/dd/rrrr') DATE1 , 133200.00 AMOUNT from dual union all
select 12111201 ACT_NO, to_date('5/19/2009','mm/dd/rrrr') DATE1 , 66600.00 AMOUNT from dual
)
select extract(year from date1) date1_year
,sum(amount) amount_summed
,count(distinct ACT_NO) distinctAccounts
, count(*) total_occurrences
from sampleData
/*where date1 between to_date('01-JAN-2009', 'DD-MON-YYYY') and to_date('31-DEC-2009 23:59:59', 'DD-MON-YYYY HH24:MI:SS')*/ --from @APC's answer
/* 在 to_date('01-JAN-2009', 'DD-MON-YYYY') 和 to_date('31-DEC-2009 23:59:59', 'DD-MON-YYYY HH24:MI:SS 之间的日期1 ')*/ --来自@APC的回答
group by extract(year from date1)
/
DATE1_YEAR AMOUNT_SUMMED DISTINCTACCOUNTS TOTAL_OCCURRENCES
---------------------- ---------------------- ---------------------- ----------------------
2008 133200 1 1
2009 199800 1 3
You may also use extract(year from dateField)
您也可以使用提取（来自日期字段的年份）
Although unless you want to create an index on the function, utilizing @APC's or @Tony Andrews' is the way to go for filtering on the year.
尽管除非您想在函数上创建索引，否则使用 @APC 或 @Tony Andrews 是对年份进行过滤的方法。
