sql - 具有按计数和排名分组的 TSQL

Question

假设我有以下内容:

DECLARE @Name TABLE (Name VARCHAR(MAX));

INSERT INTO @Name VALUES ('bob'),('bob'),('john'),('john'),('mark'),('mark'),('mark'),('lisa');

SELECT  Name,
COUNT(Name) AS TOTALS
FROM @Name
GROUP BY Name
ORDER BY TOTALS DESC;

如何使用 Rank() 或 Dense_Rank() 来根据计数获得排名？

Answer 1

如果你想要一个排名列，你可以将它与group by一起使用:

SELECT n.Name, COUNT(*) as TOTALS,
       RANK() OVER (ORDER BY COUNT(*) DESC) as Total_Rank
FROM @Name n
GROUP BY n.Name
ORDER BY TOTALS DESC;