main表 两个字段id、team
能不能按team中某数据出现次数由高到低排序并列出出现的次数例如id | team | count
-----------------------
2 |team2 | 10
1 |team1 | 8
3 |team3 | 5
能不能按team中某数据出现次数由高到低排序并列出出现的次数例如id | team | count
-----------------------
2 |team2 | 10
1 |team1 | 8
3 |team3 | 5
SELECT *,COUNT(*) AS count
FROM `main`
GROUP BY `team`,`id`
ORDER BY count DESC
id team ip
1 member 127.0.0.1
2 a 127.0.0.1
3 a 127.0.0.1
4 a 127.0.0.1
5 member 127.0.0.1
6 member 127.0.0.1
7 b 127.0.0.1
8 member 127.0.0.1
9 member 127.0.0.1
10 member 127.0.0.1
11 b 127.0.0.1想要这样
team count
member 6
a 3
b 2
SELECT `team`,COUNT(*) AS count
FROM `main`
GROUP BY `team`,`ip`
ORDER BY count DESC