mysql选择不同但最新的行 [英] mysql select distinct but latest row

问题描述

如何从表格中为每个存在的不同站点"选择最新的分数"?

How can I select the latest 'score' from a table for each distinct 'site' that exists?

例如:

site |   date   | score
 a   | 20140101 |  10
 a   | 20140102 |  8
 b   | 20140103 |  11
 b   | 20140202 |  9

我想为 a 和 b 返回一个结果，但只返回它们的最新条目(按日期).

I'd like to return one result for a and b, but only their latest entry (by date).

 a   | 20140102 |  8
 b   | 20140202 |  9

我知道如何按站点分组，或选择不同的站点，但不确定如何将范围缩小到最新日期.

I know how to group by site, or select distinct site, but not sure how to narrow it down to their latest dates.

对于数千个不同的站点，这应该是动态的.

edit: this should be dynamic for thousands of distinct sites.

推荐答案

你可以用联合来做到这一点

You can do it with a union

(SELECT 
    site,date,score 
FROM
    `table`
WHERE 
    site = 'a'
ORDER BY date DESC
LIMIT 0,1)
UNION
(SELECT 
    site,date,score 
FROM
    `table`
WHERE 
    site = 'b'
ORDER BY date DESC
LIMIT 0,1)

如果你不想写一个 UNION

SELECT 
    t.site, t.date, t.score
FROM 
    `table` t
    JOIN (
       SELECT ti.site,MAX(ti.date) AS dt
       FROM `table` ti
       GROUP BY ti.site
    ) t2 ON t2.site = t.site and t2.dt = t.date

这篇关于mysql选择不同但最新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！