背景:同事有个需求,需要按照某个字段的值划分区间,之后按照区间进行排序。第一反应是用if,后来想想if有点没转出来,就改成case...when了
SELECT goods_id, sort_order,
CASE
WHEN sort_order IS NULL THEN 0
WHEN sort_order < 80 THEN 1
WHEN sort_order BETWEEN 80 and 160 THEN 2
ELSE 3
END AS order_level
FROM ecm_market_activity_apply
WHERE goods_id > 0
ORDER BY order_level DESC,sort_order DESC
因为sql语句中用到了between...and,又有同事说between...and可能比<=,>=这样慢一些,于是用mysql的explain分析了两条sql
EXPLAIN SELECT * FROM ecm_goods WHERE goods_id BETWEEN 290000 AND 310000
EXPLAIN SELECT * FROM ecm_goods WHERE goods_id >= 290000 AND goods_id <= 310000
解析后的图如上,结论的话就见仁见智吧(我请教的大神说,sql分析一样,效率就应该是一样的,不会有区别)。