假设我有一个表格,显示一个人在 24 小时内消耗的水果类型,如下所示:
Name Fruit
Tim Apple
Tim Orange
Tim Orange
Tim Orange
Lisa Peach
Lisa Apple
Lisa Peach
Eric Plum
Eric Orange
Eric Plum
如何获得一个表格,仅显示每个人消耗最多的水果以及消耗的水果数量。换句话说,表格如下所示:
Name Fruit Number
Tim Orange 3
Lisa Peach 2
Eric Plum 2
我试过了
SELECT Name, Fruit, Count(Fruit)
FROM table
GROUP BY Name
但这会返回错误,因为 Name 也需要位于 GROUP BY 语句中。我尝试过的所有其他方法都会返回所有值的计数,而不仅仅是最大值。 MAX(COUNT()) 似乎不是一个有效的语句,所以我不知道还能做什么。
请您参考如下方法:
这很痛苦,但你可以做到。从您的查询开始,然后使用 join
:
SELECT n.Name, n.Fruit
FROM (SELECT Name, Fruit, Count(Fruit) as cnt
FROM table as t
GROUP BY Name, Fruit
) as t INNER JOIN
(SELECT Name, max(cnt) as maxcnt
FROM (SELECT Name, Fruit, Count(Fruit) as cnt
FROM table
GROUP BY Name, Fruit
) as t
GROUP BY Name
) as n
ON t.name = n.name and t.cnt = n.maxcnt;