MySQL 可以進(jìn)行order by 排序,通常后面只會(huì)接字段 如 select * from table order by name;但是如果有復(fù)合排序的時(shí)候后面會(huì)要接一些更負(fù)責(zé)的條件,單純用字段便無(wú)法滿足,如:SELECT TITLE, CJSJ, date_format(cjsj2, '%Y-%m-%d %H') '時(shí)間戳', FMJBFROM nms_key_infoWHERE CJSJ BETWEEN '2017-2-22 00:00:00'AND '2017-2-22 23:59:59'ORDER BY cjsj BETWEEN '2017-2-22 11:00:00'AND '2017-2-22 11:30:00' desc , date_format(cjsj, '%Y-%m-%d %H') DESC, FMJB DESC, CJSJ DESC這段語(yǔ)句的后面order by 后面接了一個(gè)時(shí)間的區(qū)間,是要把指定的時(shí)間區(qū)間內(nèi)的數(shù)據(jù)排到最上面,然后其余的數(shù)據(jù)按照后面排序規(guī)則經(jīng)行排序但是,這種寫法在mysql可以用,hibernate中的hql語(yǔ)句就會(huì)報(bào)錯(cuò),我測(cè)試了很久又發(fā)現(xiàn)一種寫法兩者都可以使用,SELECT TITLE, CJSJ, date_format(cjsj2, '%Y-%m-%d %H') '時(shí)間戳', FMJB, (case when CJSJ BETWEEN '2017-2-22 11:00:00' and '2017-2-22 11:30:00' then 1 ELSE 2 END ) 'lev'FROM nms_key_infoWHERE CJSJ BETWEEN '2017-2-22 00:00:00'AND '2017-2-22 23:59:59'ORDER BY(case when CJSJ BETWEEN '2017-2-22 11:00:00' and '2017-2-22 11:30:00' then 1 ELSE 2 END ) , date_format(cjsj, '%Y-%m-%d %H') DESC, FMJB DESC, CJSJ DESC使用case when 來(lái)經(jīng)行級(jí)別排序,把指定的時(shí)間區(qū)間內(nèi)的數(shù)據(jù)
