我试图获得最大计数值,但错误
这是我的数据
username tool_id
___________________
user1 1
user1 2
user2 1
user3 3
我需要获得tool_id 的最大计数
我用这个代码来获取tool_count
SELECT tool_id, COUNT(tool_id) AS tool_count
FROM table
GROUP BY tool_id
tool_id tool_count
_____________________
1 2
2 1
3 1
我用
SELECT tool_id,max(tool_count)
FROM
(
SELECT tool_id, COUNT(tool_id) AS tool_count
FROM table
GROUP BY tool_id
)
获取最大刀具计数,但它错误
我需要的结果:
tool_id tool_count
__________________
1 2
您可以尝试使用order by desc with limit 1
SELECT tool_id, COUNT(tool_id) as tool_count
from table
group by tool_id
order by tool_count desc limit 1
如果tool_id是primary_key,则从查询中删除GROUP BY,并且不需要使用此命令。
但是,如果您同时获得表的所有行和count,则需要按所有列中具有相似数据的列进行分组,您可以更改表并添加一个默认值永远不会更改的列。
最好在另一个查询中获取计数。