mysql - 仅获取具有 5 个表的最大值的行

Question

我有这个问题:

SELECT work_orders.id,work_orders.create_datetime,work_orders.location,
       work_orders.description,user_accounts.function as function,
       user_accounts.name as username,work_order_states.name as state,
       work_orders_history.state_id,max(work_order_states.id)
FROM `work_orders`
LEFT JOIN work_orders_history 
    on work_orders.id = work_orders_history.work_order_id
LEFT JOIN user_accounts 
    on work_orders.create_user_id = user_accounts.id
LEFT JOIN work_order_type 
    on work_orders_history.type_id = work_order_type.id
LEFT JOIN work_order_states 
    on work_orders_history.state_id = work_order_states.id

... 结果是这样的:

但我想要的只是获取最大 state_id 的行。

我已经尝试过各种查询，但这是我能得到的最好结果。

编辑:由于结果超过 65k，因此很难提供 fiddle 。理想的结果就像这张图片:

提前致谢。

Answer 1

您的查询看起来非常接近。您需要添加一个 GROUP BY 并删除您选择的列之一。

SELECT work_orders.id,work_orders.create_datetime,work_orders.location,
       work_orders.description,user_accounts.[function] as [function],
       user_accounts.name as username,work_order_states.name as state,
       max(work_order_states.id)
FROM `work_orders`
LEFT JOIN work_orders_history 
    on work_orders.id = work_orders_history.work_order_id
LEFT JOIN user_accounts 
    on work_orders.create_user_id = user_accounts.id
LEFT JOIN work_order_type 
    on work_orders_history.type_id = work_order_type.id
LEFT JOIN work_order_states 
    on work_orders_history.state_id = work_order_states.id

GROUP BY work_orders.id,work_orders.create_datetime,work_orders.location,
       work_orders.description,user_accounts.function,
       user_accounts.name,work_order_states.name
HAVING work_orders_states.ID = (SELECT MAX(state_id) FROM work_orders_history)