请教sql统计查询

Question

表

ID	uin	nickname
1	1234	张三
2	1234	张三
3	1234	张三
4	1234	张三
5	1234	张三
6	2345	李四
7	2345	李四
8	2345	李四
9	2345	李四
10	2345	李四
11	3456	王五

目标结果

uin_count	nickname	uin
5	张三	1234
4	李四	2345
1	王五	3456

现在只会如下

SELECT COUNT(uin) as uin_count,nickname
FROM Commin
GROUP BY nickname
ORDER BY COUNT(uin) desc

希望有人帮解答，谢谢

Answer 1

GROUP BY字段取决于索引Index(nickname, uin)还是Index(nickname)

SELECT COUNT(id) as uin_count, nickname, uin
FROM Commin
GROUP BY nickname, uin
ORDER BY uin_count DESC;

SELECT COUNT(uin) as uin_count, nickname, min(uin) AS uin
FROM Commin
GROUP BY nickname
ORDER BY uin_count DESC;

Answer 2

你也可以 group by uin。

Answer 3

SELECT COUNT(uin) as uin_count,uin,nickname
FROM Commin
GROUP BY nickname,uin
ORDER BY COUNT(uin) desc