在SQL中动态查找基于最大数目的名称

Question

我有一个这样的数据集：

表A:

Date           PlaceName              Partner             Money
2021-03-26      SITE A               PARTNER A           100
2021-04-26      SITE B               PARTNER A           200
2021-03-26      SITE A               PARTNER B           0
2021-04-26      SITE B               PARTNER B           230
2021-04-26      SITE B               PARTNER B           230
2021-03-26      SITE A               PARTNER C           0
2021-04-26      SITE B               PARTNER C           230
2021-04-26      SITE B               PARTNER C           230
...

合作伙伴花钱的最大名额是多少？这些合作伙伴是谁？仅显示达到此最大数量的合作伙伴

我试过这个：

select count(PlaceName) as num_of_sites, Partner
from (
select distinct Place, Partner
from TableA
where Money > 0
) a
group by Partner
order by count(PlaceName) desc

但我觉得这不是正确的逻辑。我错过了什么？

Answer 1

真的不需要你的子查询，没有它就可以工作：

SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
FROM TableA a
WHERE a.Money > 0 
GROUP BY a.Partner
ORDER BY COUNT(DISTINCT a.PlaceName) desc

但由于查询是如何优化的，所以有一个不会有任何区别，所以无论哪种更容易阅读都是最好的。对于子查询，它看起来像这样：

SELECT b.* 
FROM (SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
FROM TableA a
WHERE a.Money > 0 
GROUP BY a.Partner) b
ORDER BY b.num_of_sites desc

如果你只想在最高结果上显示名称，那么你想：

SELECT TOP 1 WITH TIES b.PARTNER 
FROM (SELECT COUNT(DISTINCT a.PlaceName) AS num_of_sites, a.Partner
FROM TableA a
WHERE a.Money > 0 
GROUP BY a.Partner) b
ORDER BY b.num_of_sites desc