db2 - 如何在 DB2 中按 TO_CHAR(datefield,'MM-YYYY') 排序

Question

我需要按月和年以升序格式排序。我尝试使用ORDER BY readingdate但它导致了eoor。下面是查询

 select A,B,C,(TO_CHAR(readingdate,'MM-YYYY'))mth 
 from TABLE1
 inner join TABLE2  
 left join TABLE3  
 where (Readingdate >= DATE ('2019-11-01')  
 AND Readingdate < DATE ('2020-01-31') + 1 DAY)
 group by A,B,C,TO_CHAR(readingdate,'MM-YYYY')
 order by mth;

输出

A   Mth
200  01-2020
200  11-2019
200  12-2019

预期产出

  A   Mth
 200  11-2019
 200  12-2019
 200  01-2020 

标签： db2

Answer 1

您必须按保持所需顺序的值进行分组，然后进行转换，例如：

select A,B,C, TO_CHAR(mth,'MM-YYYY') mthchar
from (
 select A,B,C, last_day(readingdate) mth
 from TABLE1
 inner join TABLE2  
 left join TABLE3  
 where (Readingdate >= DATE ('2019-11-01')  
 AND Readingdate < DATE ('2020-01-31') + 1 DAY)
 group by A,B,C,last_day(readingdate)
)
order by mth;