在T-SQL中选择桥接表中最大组/最常见值的值

Question

我有一个表格，代表另一张表中两行之间的“合同”。根据下面的数据，如何获取每个终端用户最常见的分销商？

Contracts

EndUserId | DistributerId | StartDate | EndDate 
-----------------------------------------
1         | 8             | ...       | ...
1         | 9             | ...       | ...
1         | 9             | ...       | ...
2         | 8             | ...       | ...
2         | 8             | ...       | ...
2         | 9             | ...       | ...   
3         | 8             | ...       | ...   
3         | 9             | ...       | ...   

我需要查询必须返回以下内容：

EndUserId | DistributerId
-------------------------
1         | 9
2         | 8
3         | 8 or 9, it is of no consequence.

谢谢您的提前帮助！在这里搜索并不是很有帮助，因为很难在没有样本数据的情况下描述目标。

- andrej351

Answer 1

未经测试，但我认为这将解决问题：

WITH ContractCounts AS 
( --First Get the counts for each distributer
  SELECT EndUserID, DistributerID, Count(*) As ContractCount
  FROM Contracts
  GROUP BY EndUserID, DistributerID
),
ContractMax AS 
(  -- Then find out how many contracts the largest distributed for each user had
  SELECT EndUserID, Max(ContractCount) As MaxContractCount
  FROM ContractCounts 
  GROUP BY EndUserID
)
-- and finally select only the distributor for each user who's count matches the prior query
SELECT c.EndUserID, MAX(c.DistributerID) AS DistributerID
FROM ContractCounts c
INNER JOIN ContractMax m on m.EndUserID=c.EndUserID AND c.ContractCount = m.MaxContractCount
GROUP BY c.EndUserID

- Joel Coehoorn

---

嘿，伙计，那很完美，除了在我的示例中对于EndUserId 3它将返回两行，一行是8，另一行是9。我提交了一个编辑，添加了GROUP BY和MAX，它只是获取其中一个值。谢谢！ - andrej351

@andrej351，你的建议编辑被用户拒绝了（我怀疑他们没有分析问题和答案，也没有注意到你是原始发布者）。既然这个更改是让答案对你 - 问题提问者 - 有效的，我已经为你编辑回来了。如果Joel Coehoorn不喜欢这个更改，他可以还原它。 - Brock Adams

Answer 2

select *
from
(
    select *, row_number() over(partition by EndUser order by UserDistCount desc) as rownum
    from
    (
        select EndUserId, DistributorId, count(*) as UserDistCount
        from Contracts
        group by EndUserId, DistributorId
    ) a
) b
where rownum = 1

- user596075

---

我更喜欢这个解决方案，因为：1）它本质上解决了“在平局情况下任意返回1个值”的问题；2）通过将rownum = 1更改为rownum <= n，它可以推广到其他应用程序中“返回前n个条目”的情况。 - jhchou