sql-server - 如何计算 SQL 中另一列中每个值的众数

Question

我有一个这样的表:

ID   Group  Gender
------------------
1    A      M
2    A      M
3    A      F
4    A      M
5    A      U
6    B      F
7    B      F
8    B      M
9    C      U
10   C      F
11   C      U

我正在尝试计算每个性别的模式组。换句话说，对于每个性别，告诉我哪一个是最受欢迎的群体。所以我想要的结果如下:

Gender  ModeGroup
-----------------
M       A          (because 3 males in group A, 1 in B and 0 in C)
F       B          (because 2 females in group B, 1 in A and 1 in C)
U       C          (because 2 unknown in group C, 0 in B and 1 in C)

如果是平局，我需要为每个平局组返回一条记录。

如何在 TSQL 中优雅地完成此操作？我认为我需要使用窗口函数，但我一直在努力解决如何实现它。

Answer 1

这是我的无 CTE 解决方案:

SELECT
  Gender,
  [Group]
FROM
  (
    SELECT
      [Group],
      Gender,
      RANK() OVER(PARTITION BY Gender ORDER BY [Count] DESC) AS [Rank]
    FROM
      (
        SELECT [Group], Gender, SUM(1) AS [Count]
        FROM GroupGender
        GROUP BY [Group], Gender
      ) AS counts
  ) AS ranks
WHERE
  [Rank] = 1

SQL Fiddle 演示:No ties和 With ties

更新:(见评论)