https://blog.csdn.net/codertnt/article/details/80894058

SELECT DISTINCT
detail.`id`,
conf1. NAME AS `coal_type_name`,
ord.`main_order_number`,
ord.`coal_name`,
ord.`shipper_name`,
ord.`shipper_phone`,
ord.`shipper_address`,
ord.`shipper_detail_address`,
ord.`consignee_name`,
ord.`consignee_phone`,
ord.`consignee_address`,
ord.`consignee_detail_address`,
detail.`code`,
detail.`order_code`,
(
SELECT
COALESCE (
sum(
CASE
WHEN extra.extra_pay_type = 0 THEN
abs(extra.extra_fee_amount)
ELSE
- abs(extra.extra_fee_amount)
END
),
0
)
FROM
tb_order_extra_fee extra
WHERE
extra.order_code = detail. CODE
AND extra.del_flag = 0
) AS extra_fee,
(
CASE
WHEN detail.`status` = 0 THEN
‘未接单‘
WHEN detail.`status` = 1
AND detail.is_fill = 0 THEN
‘已接单‘
WHEN detail.is_sign = 0
AND detail.is_fill = 1 THEN
‘已装货‘
WHEN detail.`status` = 3 THEN
‘已卸货‘
WHEN detail.`status` = 4 THEN
‘已核算‘
ELSE
‘已打款‘
END
) AS status_name,
detail.`status`,
detail.`create_time`,
detail.`fill_time`,
detail.`receive_time`,
detail.`sign_time`,
detail.`settle_time`,
detail.`update_time`,
detail.`pay_time`,
detail.`delivery_cash_fee`,
detail.`delivery_card_fee`,
detail.`delivery_oil_fee`,
detail.delivery_fee_deserved,
detail.`delivery_fee_practical`,
detail.shipper_delivery_fee,
detail.real_price,
detail.pay_cash_fact,
detail.pay_oil_fact,
detail.pay_steam_fact,
send_driver.`name` AS driver_name,
send_driver.`phone` AS driver_phone,
ur.`name` AS update_name,
ur.`phone` AS update_phone,
send_vehicle.license_number AS vehicle_license,
(
CASE detail_settlement.apply_status
WHEN ‘0‘ THEN
‘未申请‘
WHEN ‘1‘ THEN
‘已申请‘
WHEN ‘2‘ THEN
‘已驳回‘
WHEN ‘4‘ THEN
‘已同意‘
ELSE
‘‘
END
) AS settlement_apply_status,
detail.load_weight AS detail_load_weight,
detail.unload_weight AS detail_unload_weight,
(
SELECT
COALESCE (
sum(
CASE extra.extra_pay_type
WHEN ‘0‘ THEN
extra.extra_fee_amount
ELSE
- extra.extra_fee_amount
END
),
0
)
FROM
tb_order_extra_fee extra
WHERE
extra.order_code = detail. CODE
AND extra.del_flag = 0
) AS extrafee,
send_team.link_man AS team_name,
shipment.user_name AS shipment_name,
shipment.company_name AS shipment_company_name,
(
CASE detail.invoice_status
WHEN ‘0‘ THEN
‘未索取‘
WHEN ‘1‘ THEN
‘已索取‘
WHEN ‘2‘ THEN
‘已开票‘
WHEN ‘7‘ THEN
‘已确认收票‘
ELSE
‘‘
END
) AS invoice_status_name,
(
CASE
WHEN (
detail.`delivery_cash_fee` - detail.`pay_cash_fact` - detail.`pay_oil_fact` - detail.`pay_steam_fact`
) > 0 THEN
(
detail.`delivery_cash_fee` - detail.`pay_cash_fact` - detail.`pay_oil_fact` - detail.`pay_steam_fact`
)
ELSE
‘0‘
END
) AS not_payed_fee
FROM
tb_order_detail detail
INNER JOIN (
SELECT DISTINCT
id
FROM
tb_order_detail detail
WHERE
1 = 1
AND detail.del_flag = 0
AND detail.branch_code = ‘27adb23841d547b8ae6f0c11481b4cfd‘
ORDER BY
detail.id DESC
LIMIT 0,
10
) a USING (id)
LEFT JOIN tb_order ord ON detail.`main_order_code` = ord.`code`
LEFT JOIN tb_shipment shipment ON ord.`create_code` = shipment.`user_code`
LEFT JOIN tb_common_config conf1 ON ord.`coal_type` = conf1.`code`
LEFT JOIN tb_driver send_driver ON detail.`driver_code` = send_driver.`user_code`
LEFT JOIN tb_vehicle send_vehicle ON detail.`vehicle_code` = send_vehicle.`code`
LEFT JOIN tb_driver send_team ON send_team.user_code = detail.create_code
LEFT JOIN tb_user ur ON detail.`update_code` = ur.`code`
AND conf1.`codetype` = ‘coalType‘
LEFT JOIN tb_order_detail_settlement detail_settlement ON detail_settlement.order_detail_code = detail. CODE

mysql orderby 查询过慢优化

原文：https://www.cnblogs.com/leirenyuan/p/12617657.html