mysql先分组后排序

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 
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容