+------+-------+
| name | value |
+======+=======+
| 5 | 0 |
+------+-------+
| 4 | 0 |
+------+-------+
| 3 | 1 |
+------+-------+
| 4 | 1 |
+------+-------+
| 4 | 1 |
+------+-------+
| 5 | 0 |
+------+-------+
我希望获得每个名称部分重复次数最多的值。
名称5具有重复次数最多的值0
名称4具有重复次数最多的值1
名称3具有重复次数最多的值1
如何在对mysql的单个查询中实现此操作?
谢谢
已解决
通过@NVIDOT中的select语句和SO中的另一个帖子,我发现这是此类查询的一个常见问题。 MySQL的新版本默认情况下只启用了FULLGROUPBY,这里的许多解决方案都将在这种情况下测试失败。
所以我的工作公式是: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;