SQL Server 数据库服务器：尝试查找已计数行的最大值

arknldoa 于 2022-11-28 发布在其他

关注(0)|答案(1)|浏览(129)

我使用的是SQL Server 2012，我有这样的表

| Country | Age  |
+---------+------+
| SWEDEN  |  43  |
| SWEDEN  |  17  |
| SWEDEN  |  43  |  
| SWEDEN  |  43  |
| SWEDEN  |  17  |
| GERMANY |  17  |
| GERMANY |  17  |
| GERMANY |  17  |
| GERMANY |  44  |
| GERMANY |  44  |
| SWEDEN  |  43  |
| SWEDEN  |  17  |
| GERMANY |  13  |
| SWEDEN  |  17  |
| SWEDEN  |  43  |

我可以像这样在乡下数年龄

| Country  | Age  | CountOfAge |
+----------+------+------------+
| GERMANY  |  13  |     1      |
| GERMANY  |  17  |     3      |
| SWEDEN   |  17  |     4      |
| SWEDEN   |  43  |     5      |
| GERMANY  |  44  |     2      |

我想在这样国家里达到最大年龄

| Country  | Age  | CountOfAge |
+----------+------+------------+
| GERMANY  |  17  |    3       |
| SWEDEN   |  43  |    5       |

我尝试使用以下SQL语句：

SELECT  
    X.country, X.age, X.countOfAge
FROM 
    (SELECT country, age, COUNT(age) AS countOfAge
     FROM MOCK
     GROUP BY country, age) X

我计算了年龄，但无法筛选计算的年龄的最大值

sql-server

来源：https://stackoverflow.com/questions/74565467/sql-server-try-to-find-max-value-of-counted-row

1条答案

按热度按时间

sd2nnvve1#

在这种情况下，RANK()或DENSE_RANK()都将提供相同的结果，但请阅读它们的文档以了解两者之间行为的差异-特别是RANK（）返回的数字中的差距。

SELECT
    country,
    age,
    countOfAge
FROM (
    SELECT
        country,
        age,
        COUNT(age) AS countOfAge,
        DENSE_RANK() OVER (PARTITION BY country ORDER BY COUNT(age) DESC) AS ranking
    FROM MOCK
    GROUP BY country, age
) X
WHERE ranking = 1;

赞(0）回复(0）举报 2022-11-28

我来回答

SQL Server 数据库服务器：尝试查找已计数行的最大值

1条答案

相关问题

热门标签

最新问答