SELECT * from (SELECT
o.id AS orderId,
o.tenant_id AS tenantId,
c.create_time AS creditExtensionTime,
c.id AS orderCreditExtensionId,
c.quota AS quota,
d.draw_money_time AS drawMoneyTime,
d.id AS orderDrawMoniesId,
d.draw_money_status AS drawMoneyStatus,
d.draw_money AS drawMoney,
d.client_type AS clientType,
o.phone_number AS phoneNumber,
o.product_info_id AS productInfoId,
o.product_name AS productName,
o.apply_quota AS idCard,
o.user_id AS userId,
o.id_card AS applyQuota,
o.create_time AS createTime,
o.enterprise_name AS enterpriseName,
o.salesman_id AS salesmanId,
o.socialcredit_code AS socialcreditCode,
o.order_status AS orderStatus,
o.order_number AS orderNumber,
o.belong_user_id AS belongUserId,
c.del_flag AS cDelFlag,
o.del_flag AS delFlag,
d.del_flag AS dDelFlag,
o.contacts AS contacts
FROM
order_info AS o
LEFT JOIN order_credit_extension AS c ON o.id = c.order_id
LEFT JOIN order_draw_money AS d ON d.order_id = o.id
WHERE
o.order_type = 1
order by drawMoneyTime desc limit 1000000) t GROUP BY t.orderId
也可以换种思路
直接取排序的最大值,跳过mysql的执行顺序
SELECT
o.id AS orderId,
o.tenant_id AS tenantId,
c.create_time AS creditExtensionTime,
c.id AS orderCreditExtensionId,
c.quota AS quota,
d.draw_money_time AS drawMoneyTime,
d.id AS orderDrawMoniesId,
d.draw_money_status AS drawMoneyStatus,
d.draw_money AS drawMoney,
d.client_type AS clientType,
o.phone_number AS phoneNumber,
o.product_info_id AS productInfoId,
o.product_name AS productName,
o.apply_quota AS idCard,
o.user_id AS userId,
o.id_card AS applyQuota,
o.create_time AS createTime,
o.enterprise_name AS enterpriseName,
o.salesman_id AS salesmanId,
o.socialcredit_code AS socialcreditCode,
o.order_status AS orderStatus,
o.order_number AS orderNumber,
o.belong_user_id AS belongUserId,
o.del_flag AS delFlag,
d.del_flag AS dDelFlag,
o.contacts AS contacts
FROM
order_info AS o
LEFT JOIN order_credit_extension AS c ON o.id = c.order_id
LEFT JOIN (
SELECT
b.*
FROM
( SELECT order_id, max( order_draw_money.draw_money_time ) draw_Money_Time FROM order_draw_money GROUP BY order_id ) a
JOIN order_draw_money b ON b.order_id = a.order_id
AND b.draw_Money_Time = a.draw_Money_Time
) AS d ON d.order_id = o.id
WHERE
o.order_type = 1