在SQL中，通过获取特定组的Count()的最大值

Question

我的脚本

SELECT ans.Questions_Id,ans.Answer_Numeric,ans.Option_Id, opt.Description, count(ans.Option_Id) as [Count]
FROM Answers ans
LEFT OUTER JOIN Questions que
  ON ans.Questions_Id = que.Id
LEFT OUTER JOIN Options opt
  ON ans.Option_Id = opt.Id
WHERE que.Survey_Id = 1
    and ans.Questions_Id = 1        
GROUP By ans.Questions_Id,ans.Answer_Numeric,ans.Option_Id, opt.Description
ORDER BY 2, 5 desc

我正在尝试获得每个Answer_Numeric的前几个回复（描述）。目前的结果是这样的：

| Questions_Id | Answer_Numeric | Option_Id | Description      | Count
-----------------------------------------------------------------------
| 1            | 1              | 27        | Technology       | 183
| 1            | 1              | 24        | Personal Items   | 1
| 1            | 2              | 28        | Wallet / Purse   | 174
| 1            | 2              | 24        | Personal Items   | 3
| 1            | 2              | 26        | Spiritual        | 1
| 1            | 3              | 24        | Personal Items   | 53
| 1            | 3              | 25        | Food / Fluids    | 5
| 1            | 3              | 26        | Spiritual        | 5
| 1            | 3              | 27        | Technology       | 1
| 1            | 3              | 28        | Wallet / Purse   | 1

从上面的示例数据来看，我需要它看起来像这样：

| Questions_Id | Answer_Numeric | Option_Id | Description      | Count
-----------------------------------------------------------------------
| 1            | 1              | 27        | Technology       | 183
| 1            | 2              | 28        | Wallet / Purse   | 174
| 1            | 3              | 24        | Personal Items   | 53

我很确定我的Having子句中需要一个最大值或其他值，但我所尝试的一切都不起作用。非常感谢在这方面的任何帮助。

Answer 1

您可以使用ROW_NUMBER:

SELECT Questions_Id, Answer_Numeric, Option_Id, Description, [Count]
FROM (
  SELECT ans.Questions_Id,ans.Answer_Numeric,ans.Option_Id, 
         opt.Description, count(ans.Option_Id) as [Count],
         ROW_NUMBER() OVER (PARTITION BY ans.Questions_Id, ans.Answer_Numeric
                            ORDER BY count(ans.Option_Id) DESC) AS rn
  FROM Answers ans
  LEFT OUTER JOIN Questions que
    ON ans.Questions_Id = que.Id
  LEFT OUTER JOIN Options opt
    ON ans.Option_Id = opt.Id
  WHERE que.Survey_Id = 1
        and ans.Questions_Id = 1        
  GROUP By ans.Questions_Id,
           ans.Answer_Numeric,
           ans.Option_Id, 
           opt.Description) AS t
WHERE t.rn = 1
ORDER BY 2, 5 desc

或者，您可以使用RANK来处理关系，即每个Questions_Id、Answer_Numeric分区有多行共享相同的最大Count数。

Answer 2

使用row_number():

SELECT *
FROM (SELECT ans.Questions_Id, ans.Answer_Numeric, ans.Option_Id, opt.Description,
             count(*) as cnt,
             row_number() over (partition by ans.Questions_Id, ans.Answer_Numeric
                                order by count(*) desc) as seqnum
      FROM Answers ans LEFT OUTER JOIN
           Questions que
           ON ans.Questions_Id = que.Id LEFT OUTER JOIN
           Options opt
           ON ans.Option_Id = opt.Id
      WHERE que.Survey_Id = 1 and ans.Questions_Id = 1        
      GROUP By ans.Questions_Id, ans.Answer_Numeric, ans.Option_Id, opt.Description
     ) t
WHERE seqnum = 1
ORDER BY 2, 5 desc;

Answer 3

我们可以通过不同的方式获得相同的结果集，我已经获取了样本数据集，您只需将连接合并到该代码中

declare @Table1  TABLE 
    (Id int, Answer int, OptionId int, Description varchar(14), Count int)
;
INSERT INTO @Table1
    (Id, Answer, OptionId, Description, Count)
VALUES
    (1, 1, 27, 'Technology', 183),
    (1, 1, 24, 'Personal Items', 1),
    (1, 2, 28, 'Wallet / Purse', 174),
    (1, 2, 24, 'Personal Items', 3),
    (1, 2, 26, 'Spiritual', 1),
    (1, 3, 24, 'Personal Items', 53),
    (1, 3, 25, 'Food / Fluids', 5),
    (1, 3, 26, 'Spiritual', 5),
    (1, 3, 27, 'Technology', 1),
    (1, 3, 28, 'Wallet / Purse', 1)
;
SELECT tt.Id, tt.Answer, tt.OptionId, tt.Description, tt.Count
FROM @Table1 tt
INNER JOIN
    (SELECT OptionId,  MAX(Count)OVER(PARTITION BY OptionId ORDER BY OptionId)AS RN
    FROM @Table1
    GROUP BY OptionId,count) groupedtt 
ON 
 tt.Count = groupedtt.RN
  WHERE   tt.Count <> 5
 GROUP BY tt.Id, tt.Answer, tt.OptionId, tt.Description, tt.Count

或

select distinct Count, Description , Id , Answer from @Table1 e where 1 = 
(select count(distinct Count ) from @Table1 where
Count >= e.Count and (Description = e.Description))