我有一個這樣的資料集：
表一：
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
...
合作伙伴花錢的最大 Place 數量是多少？這些合作伙伴是誰？僅顯示達到此最大數量的合作伙伴
我試過這個：
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
但我覺得這不是正確的邏輯。我錯過了什么？
uj5u.com熱心網友回復：
真的不需要你的子查詢，沒有它它也能作業：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/361938.html
標籤：sql sql-server 查询语句