oracle 如何按月分组

zi8p0yeb  于 2023-01-12  发布在  Oracle
关注(0)|答案(1)|浏览(206)

我在查找如何按月-年分组时遇到问题。以下内容有错误,
不是按表达式分组

select 
    dcst.stateid as stateId,
    dcst.crtdt ,
    to_char(to_date(DCST.CRTDT,'DD-MON-YY HH.MI.SS."000000000" AM'),'Month') AS Month,
    to_date(DCST.CRTDT,'DD-MON-YY HH.MI.SS."000000000" AM') as datetimes
from
    TBL dcst
group by to_char(to_date(DCST.CRTDT,'MON-YY HH.MI.SS."000000000" AM'),'MON-YY')

以下是表中日期的示例(上述查询的结果,不包括分组依据):

Stateid   CRTDT                            MONTH     DATETIMES
506       22-DEC-22 06.08.17.480000000 PM  December  22-DEC-22
506       23-DEC-22 10.11.00.795000000 AM  December  23-DEC-22
853       10-JAN-23 12.30.45.212000000 AM  January   04-JAN-23

我似乎在做this thread建议的按月/年分组的正确做法。
我正在Oracle Sql Developer中进行测试,但最终将在设备上不容易访问的Microsoft SQL数据库上进行测试。
更新:我添加了我想按月计数的StateId,但是按它计数会出现错误“不是单组组函数”。如果没有添加按月分组部分,就会出现此错误。
Update 2:我试图从我的查询中删除额外的东西,并得到“这里不允许使用分组函数”。

select 
    dcst.stateid as stateId,
    dcst.crtdt 
from tbl dcst
group by count(dcst.stateid), to_char(to_date(DCST.CRTDT,'MON-YY HH.MI.SS."000000000" AM'),'MON-YY')

预期产出(我认为):

12-22
2
01-23
1
dgenwo3n

dgenwo3n1#

CREATE TABLE dcst
(STATEID, CRTDT) AS
SELECT 506, TIMESTAMP '2022-12-22 06:08:17.480000' FROM DUAL UNION ALL
SELECT 506, TIMESTAMP'2022-12-23 10:11:00.480000' FROM DUAL UNION ALL
SELECT 853,TIMESTAMP'2023-01-10 10:11:00.480000' FROM DUAL

select to_char(crtdt, 'YYYY/MM'), 
       count(*)
from dcst
group by to_char(crtdt, 'YYYY/MM')
order by to_char(crtdt, 'YYYY/MM')

TO_CHAR(CRTDT,'YYYY/MM')    COUNT(*)
2022/12    2
2023/01    1

相关问题