在 Oracle 中按月分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26863875/
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
Group by month in Oracle
提问by branches
I'm trying to group by month in oracle, but I'm getting an invalid identifier on the "YEAR" function, not sure why.
我试图在 oracle 中按月分组，但我在“YEAR”函数上得到一个无效的标识符，不知道为什么。
Here is my code:
这是我的代码：
SELECT CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4)) AS MY_DATE,
sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4))
How can I accomplish the desired grouping by month?
如何按月完成所需的分组？
回答by Sylvain Leroux
Concatenation operator in Oracle is ||. Not +:
Oracle 中的连接运算符是||. 不是+：
SQL> select 'a' +' b' from dual;
select 'a' +' b' from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select 'a' || 'b' from dual;
'A
--
ab
In addition, YEARis a MySQL function. In Oracle, you will use EXTRACT(YEAR FROM ....)
另外，YEAR是一个MySQL函数。在 Oracle 中，您将使用EXTRACT(YEAR FROM ....)
Finally, are you aware of the TO_CHARfunction?
最后，你知道这个TO_CHAR功能吗？
So you should rewrite your whole query as:
因此，您应该将整个查询重写为：
SELECT TO_CHAR(day_date, 'MM-YYYY') AS MY_DATE,
sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY TO_CHAR(day_date, 'MM-YYYY')
