哪位大神 可以幫我看一下，這條 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
uj5u.com熱心網友回復：
SELECT shipper_code,logistic_code from tp_express where (subscribe_state=2 and is_subscribe = 0) or (subscribe_state=0 and is_subscribe = 0) ORDER BY receive_time
試試這個可以不
uj5u.com熱心網友回復：
同一張表，根據 subscribe_state=0 不同， 排序時間也不同；你寫的 就用一個時間排序嗎？uj5u.com熱心網友回復：
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
這樣應該可以
uj5u.com熱心網友回復：
不知道行不行沒測驗：
SELECT shipper_code ,
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
uj5u.com熱心網友回復：
--按照你寫的陳述句可以看出每個結果集有自己的排序 只是單純的做個拼接
--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
uj5u.com熱心網友回復：
如果 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/112412.html
標籤：MySQL