sql利用case...when...对非数值字段进行排序

在项目中遇到需要对非数字类型的字段状态进行排序，怎么能给字符串类型转换成对应的数值类型再进行排序呢？

('waiting2', "等待leader审批"),  ('fail', "审批失败"),('waiting', "等待管理员审批"),('configuring', "管理员正在配置"), ('configured', "配置完成"),('modify', "修改配置"), ('recycling', "正在回收"), ('done', "已回收")

于是发现了sql中超好用的一个函数case...when...

case when 可以给满足不同的状态的条件不同的值，这样可以很方便的满足我们排序分类聚合的需求

SELECT sj.*, (case progress when 'waiting' then 1 when 'configuring' then 2 when 'configured' then 3    when 'pass' then 4 when 'fail' then 5  when 'modify' then 6 when 'recycling' then 7 when 'done' then 8 end) as po FROM `support_job` sj 
order by po, transfer_at asc

当然python里面也是支持case...when...的，只要import就行啦

po_order =Case(When(progress = 'waiting2' ,then = 1),
             When(progress="waiting",then=2),
             When(progress='configuring', then=3),
             When(progress='configured', then=4),
             When(progress='pass', then=5),
             When(progress='fail', then=6),
             When(progress='modify', then=7),
             When(progress='recycling', then=8),
             When(progress='done',then=9))
job_obj = models.Job.objects.filter(owner=models.UserProfile.objects.get(user=user)).filter(enabled=True).order_by(po_order,"-created_at")