如何有效率地取得MySQL Group By查詢中每個群組的最後一行？

Optimizing Last Row Fetching in MySQL Group By Queries

In MySQL, retrieving the last row for each group of a grouped data set requires an efficient approach. One common strategy is the correlated subquery method:

select * 
from foo as a
where a.id = (select max(id) from foo where uid = a.uid group by uid)
group by uid;

While this method works, it can be computationally expensive. A more efficient alternative is to use a join with a subquery, as demonstrated in the following query:

SELECT t1.* FROM foo t1
JOIN (SELECT uid, MAX(id) id FROM foo GROUP BY uid) t2
ON t1.id = t2.id AND t1.uid = t2.uid;

This approach optimizes performance by pre-calculating the maximum ID for each group in the subquery. By joining on this pre-calculated value, the query can efficiently retrieve the last row for each group.

Additionally, using EXPLAIN on both queries would provide valuable insights into the execution plan and resource consumption, allowing for further optimization. Another alternative is to use the LEFT JOIN method:

SELECT t1.* FROM foo t1
LEFT JOIN foo t2
ON t1.id < t2.id AND t1.uid = t2.uid
WHERE t2.id is NULL;

This query identifies the last row for each group by left joining on subsequent rows and filtering out those with non-null ID values. Ultimately, the choice of optimization approach depends on the specific data and performance requirements of the application.

以上是如何有效率地取得MySQL Group By查詢中每個群組的最後一行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！