为mysql中具有相同值的每一行计算列中的重复值

Question

+------+-------+
| name | value |
+======+=======+
| 5    | 0     |
+------+-------+
| 4    | 0     |
+------+-------+
| 3    | 1     |
+------+-------+
| 4    | 1     |
+------+-------+
| 4    | 1     |
+------+-------+
| 5    | 0     |
+------+-------+            

我想为部分中的每个名称获得重复次数最多的值
name 5具有最重复的值0
name 4具有最重复值1
name 3具有最重复数值1
如何在对mysql的单个查询中做到这一点
谢谢

SOLVED

使用@nvidot中的select语句和SO中的另一个posts，我发现这是此类查询的常见问题。MySQL的较新版本默认启用ONLY_FULL_GROUP_BY，这里的许多解决方案在测试中都会失败。

所以我的工作公式是：

SELECT DISTINCT t1.name, MAX(t1.occ), MAX(t2.value)
FROM (select name, value, count(*) as occ from `table` group by name, value order by occ desc) AS t1
JOIN (select name, adstatus, count(*) as occ from `table` group by name, value order by occ desc) AS t2 ON t2.name = t1.name AND t2.occ = (
SELECT MAX(occ) FROM (select name, value, count(*) as occ from `table` group by name, value order by occ desc) t3 WHERE t3.name = t1.name
)
GROUP BY t1.name;

Answer 1

在Oracle的PL/Sql中，有一个特定的功能可以满足您的请求，它被称为Window函数，但在MySql中，直到MySql-8.0 才有这样的功能

Answer 2

SELECT       `column`,
COUNT(`column`) AS `value_occurrence` 
FROM     `my_table`
GROUP BY `column`
ORDER BY `value_occurrence` DESC

此外，请访问此链接以了解更多信息。

Answer 3

select name, val
from (select name, val, max(occ)
from (select name, val, count(*) as occ
from `sample` group by name, val
order by occ desc) as groups
group by name) as maximums;

最外层的select用作装饰，仅显示name和valorder by occ desc用于获得正确的val

以下内容可能就足够了：

select name, val
from (select name, val, count(*) as occ
from `sample`
group by name, val
order by occ desc) as groups
group by name;

[edit]：以下不应触发错误，因为它不使用非聚合列，也不依赖于order by。如果name/val计数存在多个最大值，则可能存在多个name行。

select name,val
from (select name as maxname, max(occ) as maxocc
from (select name, val, count(*) as occ
from `sample`
group by name, val) as counts2
group by name) as maxs
join (select name, val, count(*) as numocc
from `sample`
group by name, val) as counts1
on name = maxname AND numocc = maxocc;