存储过程去重需求：按公司保留指定发票行记录

我正在开发一款存储过程（SP），用于生成特定会员类型的开票记录列表。目前先针对单个公司调试（后续将支持多会员类型，本次示例为'Buyer Membership'）。

当前每个公司包含多个分支，每个分支会返回3条发票行描述记录：

Branch Monthly Membership
Buyer Monthly Membership
Carrying Charge

现有返回数据如下：

Company                                         Invoice Line
Id     CompanyName   MembershipType  BranchName Description
20     DeluxPainting BuyerMemberShip Branch1  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch1  Buyer Monthly Membership
20     Deluxainting  BuyerMemberShip Branch1  Carrying Charge
20     DeluxPainting BuyerMemberShip Branch2  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch2  Buyer Monthly Membership
20     DeluxPainting BuyerMemberShip Branch2  Carrying Charge
20     DeluxPainting BuyerMemberShip Branch3  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch3  Buyer Monthly Membership
20     DeluxPainting BuyerMemberShip Branch3  Carrying Charge

我需要进一步处理数据，要求：

• 每个公司仅保留1条'Buyer Monthly Membership'记录
• 每个公司仅保留1条'Carrying Charge'记录
• 每个分支保留1条'Branch Monthly Membership'记录

期望输出数据如下：

Company                                         Invoice Line
Id     CompanyName   MembershipType  BranchName Description
20     DeluxPainting BuyerMemberShip Branch1  Buyer Monthly Membership
20     Deluxainting  BuyerMemberShip Branch1  Carrying Charge
20     DeluxPainting BuyerMemberShip Branch1  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch2  Branch MonthlyMembership
20     DeluxPainting BuyerMemberShip Branch3  Branch MonthlyMembership

始终无法实现这一最终需求（移除分支2和3的Buyer Monthly Membership与Carrying Charge记录），请问是否可行？恳请帮助。

感谢协助，我添加了您建议的排名函数后，第一个查询能正常显示DescriptionGroup和BranchGroup，但第二个查询提示cteFinal为无效对象名。我尝试存入临时变量，或复制SP内容到新表后可执行简单查询，但希望按示例方式修复。请帮忙修正以下存储过程：

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    with PCTE AS 
(
SELECT  Count(*) as Count, Companies.Id as companyid, Branches.id as branchid, c.name as InventoryCenter
FROM            dbo.InventoryItems INNER JOIN
                dbo.inventoryCategories on InventoryItems.inventoryCategoryID = inventoryCategories.id Inner JOIN
                dbo.InventoryCenters c on inventoryCategories.InventoryCenterId = c.Id INNER JOIN
                dbo.Branches ON InventoryItems.BranchId = Branches.Id INNER JOIN
                dbo.Companies ON Branches.CompanyId = Companies.Id
        WHERE 
          InventoryItems.Deleted = 'False'AND
          Branches.IsDeleted= 'False' AND
          Companies.IsDeleted = 'False'  AND
          IsShownToMembers = 'True' AND
          Companies.Id = 20 
        group by Companies.Id, Branches.id , c.name 

)
, CompanySummaryCTE AS
(
    select companyid, branchid, [Part Center], [Equipment Center], [Attachment Center], [Powertrain Center], [Salvage Center] 
        from PCTE
    PIVOT (Sum(Count) for InventoryCenter IN ([Part Center], [Equipment Center], [Attachment Center], [Powertrain Center], [Salvage Center] )) as P
)
, cteInvoices AS (
        select t.CompanyId, t.FirstRun, t.NextRun, f.DisplayedAs as Frequency
            , l.Description as InvoiceLineDescription, l.Quantity as InvoiceLineQuantity, l.DiscountAmount as InvoiceLineDiscount
            , l.ServiceId as InvoiceLineServiceId
            from RecurringInvoiceTemplates t
            inner join RecurringInvoiceFrequencies f on t.RecurringInvoiceFrequencyId = f.Id
            inner join RecurringInvoiceLines l on l.RecurringInvoiceTemplateId = t.Id
    )
, cteFinal AS (
    SELECT Companies.Id as CompanyId, Companies.Name AS CompanyName, p.Name as MembershipType,Branches.Name as BranchName, I.InvoiceLineDescription
    FROM   
    CompanySummaryCTE cte
    JOIN Companies ON cte.companyid = companies.Id
    JOIN Branches on Branches.CompanyId = Companies.Id
    JOIN CompanyProducts cp on companies.id = cp.companyid
    JOIN Products p on cp.ProductId = p.Id
    LEFT JOIN cteInvoices I on Companies.Id = i.CompanyId
    where 
    Companies.IsDeleted = 0
    and Branches.IsDeleted = 0
    and p.ProductTypeId = 2
) 

 --Original data with magic numbers added based on partition functions
SELECT DISTINCT * , 
DENSE_RANK() OVER (PARTITION BY BranchName ORDER BY BranchName, InvoiceLineDescription ) DescriptionGroup,
DENSE_RANK() OVER (PARTITION BY InvoiceLineDescription ORDER BY BranchName) BranchGroup
FROM cteFinal
WHERE 1=1
order by CompanyId 

--Insert into 

-- Once you have the magic numbers worked out, just filter out the rows you don't want
SELECT * FROM (
SELECT * , 
ROW_NUMBER() OVER (PARTITION BY BranchName ORDER BY BranchName, InvoiceLineDescription ) DescriptionGroup,
ROW_NUMBER() OVER (PARTITION BY InvoiceLineDescription ORDER BY BranchName) BranchGroup
FROM cteFinal
) F
WHERE F.BranchGroup = 1 OR F.DescriptionGroup = 1
--ORDER BY F.BranchName, F.InvoiceLineDescription
    

END

内容的提问来源于stack exchange，提问作者John