场景,订单主表和订单跟踪表是一对多关系
以订单id = 172448为例
1.连表查询如下:
select ct.id,f.flow_type_name,f.flow_time from o2o_contract_order ct join o2o_user_repair_order_flow as f on f.order_id = ct.id
where
f.order_id =172448;
order_id flow_type_name flow_time id
172448 一键下单 1595570323 37549
172448 二次上门 1595570590 37550
172448 申请配件 1595570591 37551
172448 选择发货仓 1595570668 37552
想要关联 flow_time最大的那条记录
正常联表查询:select ct.id,f.flow_type_name,f.flow_time from o2o_contract_order ct join o2o_user_repair_order_flow as f on f.order_id = ct.id
where
f.order_id =172448;=》
172448 一键下单 1595570323 37549
会得出id最小的那一条。
解决办法:
select ct.id,f.flow_type_name,f.flow_time,f.id from o2o_contract_order ct join (select * from (select * from o2o_user_repair_order_flow order by `flow_time` DESC) t
GROUP BY t.`order_id` order by `flow_time` DESC) as f on f.order_id = ct.id
where
f.order_id =172448
GROUP BY ct.id;
得到结果
172448 选择发货仓 1595570668 37552