table1id name
1 AAAAAA
2 BBBBBB
3 CCCCCC
4 AAAAAA
5 BBBBBB
5 AAAAAA
查询结果id name counts
1 AAAAAA 3
2 BBBBBB 2
3 CCCCCC 1
统计name出现的次数,以name数量倒序排序。 在结果里过滤name重复出现。
1 AAAAAA
2 BBBBBB
3 CCCCCC
4 AAAAAA
5 BBBBBB
5 AAAAAA
查询结果id name counts
1 AAAAAA 3
2 BBBBBB 2
3 CCCCCC 1
统计name出现的次数,以name数量倒序排序。 在结果里过滤name重复出现。
as (
SELECT 1,'AAAAAA'
UNION ALL
SELECT 2,'BBBBBB'
UNION ALL
SELECT 3,'CCCCCC'
UNION ALL
SELECT 4,'AAAAAA'
UNION ALL
SELECT 5,'BBBBBB'
UNION ALL
SELECT 5 ,'AAAAAA'
)
SELECT ROW_NUMBER()OVER(ORDER BY name )id, name,COUNT(1) counts FROM test
GROUP BY name
ORDER BY COUNT(1) DESC /*
id name counts
-------------------- ------ -----------
1 AAAAAA 3
2 BBBBBB 2
3 CCCCCC 1(3 行受影响)
*/
如果我还有其它的字段想要显示呢?比如value
GROUP BY name ,valueORDER BY COUNT(1) DESC