Group by finds the latest status of the order join

Order:sn

Procedures:sn,status

1. There are order table and flow table.

The order table contains the details of the order [assuming there is no order status], and each order has a variety of states: paid, in process, pending receipt, and so on. The current demand may be to query the order status of all orders that are waiting to be received.

[answer] first find all the orders whose latest status is to be received, then make a left join with the order form, and you can get it.

First, look for all orders whose latest status is waiting to be received.

Originally patted the head to think that group by order by took the first one, and then tried it, and found that what group by got back is not the latest status, but the first one after the grouping. This is because the execution order of statements in MySQL is to execute group by first and then order by. Obviously, it's impossible to get the latest one. We can find out the latest status of each order in this way.

Select sn,max (status) from procedures group by sn.

The correct thing is to write it in the following way: do a join with yourself, and finally with order join.

SELECT a.id, a.sn, a.createdpaper.type FROM (SELECT sn, max (created_at) created_at FROM procedures GROUP BY sn) b JOIN procedures an ON a.sn = b.sn AND a.created_at = b.created_at where a.type=11

Select c. Writing journal tmp.type from complaints as c right join (

SELECT a.id, a.sn, a.createdpaper.type FROM (SELECT sn, max (created_at) created_at FROM procedures GROUP BY sn) b JOIN procedures an ON a.sn = b.sn AND a.created_at = b.created_at where a.type=11

) as tmp on tmp.sn=c.sn