Oracle SQL日期格式转换与按日期排序：解决图表X轴乱序问题

问题根源在于你用to_char把日期转成字符串后，排序逻辑变成了字符串字典序，而非真实的时间顺序——比如字符串"02-Dec-2022"会排在"01-Jan-2023"前面，但实际日期后者更晚，导致X轴顺序错乱。

不用替换to_char，只需在查询中同时保留用于排序的原始截断日期和用于显示的格式化字符串，就能兼顾显示需求和排序正确性，修改后的代码如下：

select project_name, Dates, sum(records_number) as total_records
from (
select project_name, 
  -- 格式化后的日期字符串，用于柱状图显示
  case 
    when :P33_Date_Range = 'Monthly' then
      to_char(date_sys, 'Month YYYY')  -- 追加年份，避免跨年份同月份数据合并
    when :P33_Date_Range = 'Daily' then
      to_char(date_sys, 'DD-Mon-YYYY') 
    when :P33_Date_Range = 'Weekly' then
      to_char(TRUNC(date_sys, 'IW'), 'DD-Mon-YYYY')
  end as Dates,
  -- 截断到对应粒度的原始日期，用于排序和分组
  case 
    when :P33_Date_Range = 'Monthly' then
      TRUNC(date_sys, 'Month')
    when :P33_Date_Range = 'Daily' then
      TRUNC(date_sys, 'DD')
    when :P33_Date_Range = 'Weekly' then
      TRUNC(date_sys, 'IW')
  end as sort_date,
  records_number
from BATCH
WHERE date_sys BETWEEN TO_DATE('01/01/2022', 'DD/MM/YYYY') AND TO_DATE('01/01/2023', 'DD/MM/YYYY')
) my_records
group by project_name, sort_date, Dates
order by project_name, sort_date
;

关键改动说明 #

• 新增sort_date字段：根据日期范围参数，将date_sys截断到对应粒度（月/日/周），保留原始日期类型，排序时会严格按时间顺序排列。
• 分组逻辑：同时按project_name、sort_date和Dates分组，由于Dates是sort_date的格式化结果，不会出现分组冲突，保证统计结果准确。
• 排序逻辑：用sort_date替代原有的Dates排序，彻底解决字符串排序导致的顺序混乱问题。
• 补充优化：给TO_DATE添加格式掩码'DD/MM/YYYY'，避免因数据库日期格式配置不同出现转换错误；Monthly格式追加年份，避免跨年份同月份数据被错误合并。

内容的提问来源于stack exchange，提问作者SunnyMoon