2024-03-05换成对应的日期参数

WITH  LS AS (

SELECt 1 AS ID , '2024年3月5日' AS RIQI, 1500 AS CHANCH FROM DUAL 

UNION ALL 

SELECt 5 AS ID , '2024年3月6日' AS RIQI, 1600 AS CHANCH FROM DUAL 

UNION ALL 

SELECt 9 AS ID , '2024年3月7日' AS RIQI, 1700 AS CHANCH FROM DUAL 

UNION ALL 

SELECt 13 AS ID , '2024年3月8日' AS RIQI, 1800 AS CHANCH FROM DUAL 

UNION ALL 

SELECt 17 AS ID , '2024年3月9日' AS RIQI, 1800 AS CHANCH FROM DUAL 

)

SELECT

'日' AS FL,

TO_CHAR(TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ),'yyyy-MM-dd') AS RQ ,

SUM(CHANCH) AS CHANCH

FROM

LS

WHERE 

TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ) >= TO_DATE('2024-03-05', 'YYYY-MM-DD' )

AND TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ) < TO_DATE('2024-03-05', 'YYYY-MM-DD' )+5

GROUP BY 

TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' )

UNION ALL 

SELECT

'周' AS FL,

  TO_CHAR(TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ), 'YYYY-IW') AS week_number,

  SUM(CHANCH) AS CHANCH

FROM

  LS

WHERE

TO_CHAR(TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ), 'YYYY-IW') = TO_CHAR(TO_DATE('2024-03-05', 'YYYY-MM-DD' ), 'YYYY-IW')

GROUP BY

  TO_CHAR(TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ), 'YYYY-IW')

UNION ALL 

SELECT

'月' AS FL,

  TO_CHAR(TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ), 'YYYY-MM') AS week_number,

  SUM(CHANCH) AS CHANCH

FROM

  LS

WHERE

TO_CHAR(TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ),'YYYY-MM') = SUBSTR('2024-03-05', 1,7 )

GROUP BY

  TO_CHAR(TO_DATE( RIQI, 'YYYY"年"MM"月"DD"日"' ), 'YYYY-MM')

Oracle 按天，周，月，年统计数据--https://blog.csdn.net/weixin_55747284/article/details/131229737