period字段是2010M9，2010M11等类似的数据，如果按照period desc排序的话，那么就会乱了，所以想到了用一个伪列来做，这种伪列只适用于数据量不太大，比如不超过300W的情景，否则会很慢的。

select a.*
from(
    select sul.ind_cd, sul.rpt_cd, sul.period_type, sul.period, sul.up_time,
        case
            when length(sul.period)=4 and period_type='Y' then sul.period
            when length(sul.period)=4 and period_type='M' then concat(sul.period,'M00')
            when length(sul.period)=5 and period_type='M'then concat(sul.period,'00')
            when length(sul.period)=6 and period_type='M'then concat(left(sul.period,5),'0',right(sul.period,1))
            when length(sul.period)=7 and period_type='M'then sul.period
            when length(sul.period)=1 and period_type='M'then concat(sul.period,'000M00')
            when length(sul.period)=2 and period_type='M'then concat(sul.period,'00M00')
            when length(sul.period)=3 and period_type='M'then concat(sul.period,'0M00')
            else sul.period end period_order
    from csf.sector_update_log sul
    where up_time > '' and up_time < ''
)a order by a.ind_cd asc,a.rpt_cd asc, a.period_order desc limit 1000000;

ITPub博客

增加一个伪日期字段，方便排序！

最新文章