In actual business , We often encounter the problem of how to get the latest data from the same value of the same field . After self accumulation and Baidu , Record common methods .
Specific examples
Due to the large amount of data in the table used , So only part of the data is shown
<>group by The way
First kind
SELECT order_id, max( product_price ) AS product_price FROM oms_order_item
GROUP BY order_id
Results obtained
be careful ：
SELECT id, order_id, max( product_price ) AS product_price FROM oms_order_item
GROUP BY order_id
As above SQL, If you want to get the data of other fields , And directly in SQL Add this field to the statement , The query results are inconsistent （product_price
The largest piece of data corresponds to id Should be 25, And found out id yes 21（ By default, the value of the first data after grouping is displayed ））, Attention required . Due to limited capacity , I don't know the specific reason . If a big man knows , Hope to give an answer .
The second kind
SELECT a.id, a.order_id, a.product_price FROM ( SELECT * FROM oms_order_item
ORDER BY product_price DESC LIMIT 10000 ) a GROUP BY order_id
Because in mysql5.7 When , The sorting of subqueries has become invalid , It may be that most sub queries are used as a result for the main query , The reason why sub queries do not need sorting .
therefore , Sort sub queries limit limit , At this time, the sub query is not only sorting , The sorting will take effect , But the limit can only be set as large as possible .
<> Window function mode
Ordinal function ：ROW_NUMBER(),RANK(),DENSE_RANK()
SELECT a.id, a.order_id, a.product_price FROM ( SELECT order_id, ROW_NUMBER ( )
OVER ( PARTITION BY order_id ORDER BY product_price DESC ) AS price_order,
product_priceFROM t_score ) a
Head tail function ：FIRST_VALUE(expr),LAST_VALUE(expr)
SELECT id, order_id, product_price, FIRST_VALUE ( product_price ) OVER w AS
first_price, LAST_VALUE ( product_price ) OVER w AS last_price FROM
oms_order_item WINDOW wAS ( PARTITION BY order_id ORDER BY product_price )
because mysql Version too low （8.0 And above can use window functions ）, unavailable . Just record the usage .
Technology