MySQL 可以进行order by 排序,通常后面只会接字段
如 select * from table order by name;
但是如果有复合排序的时候后面会要接一些更负责的条件,单纯用字段便无法满足,如:
SELECT
TITLE,
CJSJ,
date_format(cjsj2, '%Y-%m-%d %H') '时间戳',
FMJB
FROM
nms_key_info
WHERE
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
这段语句的后面order by 后面接了一个时间的区间,是要把指定的时间区间内的数据排到最上面,然后其余的数据按照后面排序规则经行排序
但是,这种写法在mysql可以用,hibernate中的hql语句就会报错,我测试了很久又发现一种写法两者都可以使用,
SELECT
TITLE,
CJSJ,
date_format(cjsj2, '%Y-%m-%d %H') '时间戳',
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_info
WHERE
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 来经行级别排序,把指定的时间区间内的数据
