sql - 在 Oracle SQL 中按日期的一部分(即年和月)分组

Question

我想按地区(定义为 501,502,503...)以及年份和月份汇总数据值。年份和月份来自数据库中的日期字段。所以实际上我想得到以下信息，但我得到的是每个区域每天的总计:

Area   Date      Value
501    10-2013   100000
501    11-2013   120000
502    10-2013   200000
502    11-2013   222000
...

这是 SQL。

SELECT BUSINESS_ENTITY.BUSINESS_ENTITY_NAME, to_char(DATA_POINT_DETAIL.EFF_DATE,'MM-YYYY') as TONNAGE_DATE, sum(DATA_POINT_DETAIL.NUMERIC_VALUE) as ACTUAL

 FROM   OIS.BUSINESS_ENTITY BUSINESS_ENTITY INNER JOIN (((OIS.DATA_POINT_DETAIL DATA_POINT_DETAIL INNER JOIN OIS.DATA_POINT_HEADER DATA_POINT_HEADER ON DATA_POINT_DETAIL.DATA_POINT_ID=DATA_POINT_HEADER.DATA_POINT_ID) INNER JOIN OIS.CN_DATA_POINT_BUSINESS_ENTITY CN_DATA_POINT_BUSINESS_ENTITY ON DATA_POINT_HEADER.DATA_POINT_ID=CN_DATA_POINT_BUSINESS_ENTITY.DATA_POINT_ID) INNER JOIN OIS.BUSINESS_ENTITY_ANCESTOR BUSINESS_ENTITY_ANCESTOR ON (CN_DATA_POINT_BUSINESS_ENTITY.BUSINESS_ENTITY_ID=BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ID) AND (CN_DATA_POINT_BUSINESS_ENTITY.HIERARCHY_ID=BUSINESS_ENTITY_ANCESTOR.HIERARCHY_ID)) ON BUSINESS_ENTITY.BUSINESS_ENTITY_ID=BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ID

WHERE DATA_POINT_DETAIL.EFF_DATE>={?BeginDate} and  DATA_POINT_DETAIL.EFF_DATE<={?EndDate}
AND BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ANCESTOR_ID = 57570 
AND (DATA_POINT_HEADER.DATA_CATEGORY_NAME ='Tons Cut') and DATA_POINT_DETAIL.NUMERIC_VALUE<>0 and BUSINESS_ENTITY.BUSINESS_ENTITY_NAME<>'Ore'

group by BUSINESS_ENTITY.BUSINESS_ENTITY_NAME, DATA_POINT_DETAIL.EFF_DATE

我无法为字段 TONNAGE_DATE 按字段分组。我尝试了很多不同的变体，但都失败了。

这是从 Crystal Report 在 ORACLE 数据库上运行的。

感谢任何帮助，

德怀特

Answer 1

将您的 group by 子句更改为:

group by BUSINESS_ENTITY.BUSINESS_ENTITY_NAME,
         to_char(DATA_POINT_DETAIL.EFF_DATE,'MM-YYYY');

您的查询按日期分组(大概是按天)，然后格式化输出以包含月份和年份。