哪位大神 可以帮我看一下,这条 SQL,还能优化吗?select * from (select * from (SELECT shipper_code,logistic_code from tp_express where subscribe_state=2 and is_subscribe = 0 ORDER BY subscribe_time) t1 union all select * from (SELECT shipper_code,logistic_code from tp_express where subscribe_state=0 and is_subscribe = 0 ORDER BY receive_time) t2)t3 limit 0,10
解决方案 »
- Mysql 存储结果 运行方式都有哪些
- mysql存储过程中能有默认参数么?
- 一条sql语句
- 为什么我插入数据,然后查询发现最近插入的记录在最上边?
- 实现阿拉伯数字转为中文的MYsql函数
- OReIlly.High.Performance.MySQL.Second.Edition.Jun.2008这本书中文版有么?
- ◆请问“where f3 & 17 = 0”是啥意思啊?
- 怎样将mysql数据库发布到网上?
- 请教:局域网中如何登录到服务器的MySQL?(急!在线等待)
- 〓 〓 〓 〓 MySQLDB的菜鸟问题 〓 〓 〓 〓
- mysql 我居然找不到datadir
- Mysql分组查询取每组前N条数据
SELECT shipper_code,
logistic_code
FROM tp_express
WHERE (subscribe_state = 2 OR subscribe_state = 0)
AND is_subscribe = 0
ORDER BY subscribe_time
limit 0,10这样应该可以
logistic_code
FROM tp_express
WHERE subscribe_state IN ( 0, 2 )
AND is_subscribe = 0
ORDER BY CASE WHEN subscribe_state = 2 THEN subscribe_time
ELSE receive_time
END
limit 0,10
--按照你写的语句可以看出每个结果集有自己的排序 只是单纯的做个拼接
--union后保持各自原有排序,因此subscribe_state为2的会排在前面,其次是subscribe_state = 2的再按subscribe_time排序,为0的按receive_time
--综合一下就是这样了
SELECT shipper_code,
logistic_code
FROM tp_express
WHERE (subscribe_state = 2 OR subscribe_state = 0)
AND is_subscribe = 0
ORDER BY subscribe_state desc,CASE WHEN subscribe_state = 2 THEN subscribe_time
ELSE receive_time
END
limit 0,10
如果满足条件的记录少,则条件字段加上索引,确保能够快速筛选数据就行了
如果满足条件的记录多,则比较麻烦此,因为你是在两个字段上排序,通常建议改表结构,在表上加一个生成列
alter table tp_express add order_date as(
case when subscribe_state=2 and is_subscribe = 0 then subscribe_time
when subscribe_state=0 and is_subscribe = 0 then receive_time end)这样你只需要在这个计算列上建个索引,就可以搞定排序和条件
SELECT shipper_code,logistic_code from tp_express
where order_date is not null
order by order_date
limit 0, 10