关于sql的分组查询问题

Question

这是我的一个sql，我想要把state为on_publish的结果优先排在前面，但是其中state的状态还有on_close,on_connect等，请问我的sql应该怎么改

 SELECT 
  * 
FROM
  camera_info ci,
  live_schedule l,
  camera_status cs 
WHERE ci.`cameraId` = l.`cameraId` 
  AND ci.`cameraId` = cs.`cameraId` 
  AND LEFT(l.`creatTime`, 10) = CURDATE() 
ORDER BY cs.`state` = 'on_publish' DESC

Answer 1

SELECT

• FROM camera_info ci, live_schedule l, camera_status cs WHERE ci.cameraId = l.cameraId AND ci.cameraId = cs.cameraId AND LEFT(l.creatTime, 10) = CURDATE() ORDER BY case cs.state when 'on_publish' then 1 else 2 END 这是当为on_publish 时排第一 其他排后面