mysql - MySQL:结合工会和潜在顾客的前三名

我正在尝试获取每个广告系列类型以及每个广告系列类型的出席人数最多（前3名）的三个线索名称。它看起来应该像这样：

+-------+-----------+
| Code  |  LeadName |
+-------+-----------+
| WP    | LEAD A    |
| WP    | LEAD B    |
| WP    | LEAD C    |
| CF    | LEAD B    |
| CF    | LEAD C    |
| CF    | LEAD A    |
| TL    | LEAD B    |
| TL    | LEAD A    |
| TL    | LEAD C    |


Campaign Table: CampaignID, CampaignName, CampaignStartDate, Code
Campaign Members Table:CampaignID, LeadID, CampaignMemberStatus, CampaignMemberSignUpDate
Leads Table: LeadID, LeadName, AccountID

CampaignID链接Campaign->成员，LeadID链接成员-> Leads
我正在努力结合和提供前3个LeadName。

最佳答案

SELECT TOP 3 ABC.Code,ABC.LeadName
FROM
(
SELECT A.Code,C.LeadName,COUNT(*) as total
FROM Campaign as A
LEFT JOIN [Campaign Members] as B
ON B.CampaignID = A.CampaignID
LEFT JOIN Leads as C
ON C.LeadID = B.LeadID
GROUP BY A.Code, C.LeadName
ORDER BY COUNT(*)
) as ABC