如何在SQL存储过程中提取不同行的佣金作为独立值

现有SQL存储过程[ebs].[DB_Task_ONEYCRPRaport]用于生成业务报表，佣金数据存储在[ebs].[FTOS_TPM_PreInvoiceDetail]表中，同一合同的不同佣金以独立行的形式存储（示例值如20、5）。需要在该存储过程中，将这些不同行的佣金提取为独立的返回字段。此前尝试创建新存储过程的方案效果不佳，寻求合理的实现方式。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER    PROCEDURE [ebs].[DB_Task_ONEYCRPRaport]
    --@startDate      DATE,
    --@endDate      DATE 
    
AS
BEGIN

SELECT DISTINCT
CAST(A.ExternalMerchantId as varchar) "Merchant_guid", -- ok
CAST(C.CreatedOn AS DATE) "Purchase_date", --ok
CAST(C.CreatedOn AS TIME) "Purchase_hour", --ok
C.ContractNo "Funding_reference",--ok
APP.OrderNo "External_reference", --ok
A.CustomerInternalId "Customer_external_code",--ok
CASE WHEN I.TotalAmountToPay = 0 THEN '-' ELSE '+' END "Total_amount_symbol",--ok
CASE WHEN I.TotalAmountToPay = 0 THEN I.TotalAmountToRecover ELSE I.TotalAmountToPay END "Total_amount"
from ebs.Account as A
JOIN ebs.FTOS_CB_Contract AS C ON A.Accountid = C.CustomerId
JOIN ebs.FTOS_CB_BankAccount AS BA ON BA.FTOS_CB_BankAccountid = C.MainBankAccountId
JOIN ebs.FTOS_CB_BankAccountOperation AS BAO ON BAO.BankAccountId = BA.FTOS_CB_BankAccountid
JOIN Ebs.FTOS_CMB_Currency AS CU ON CU.FTOS_CMB_Currencyid = C.CurrencyId
JOIN ebs.FTOS_TPM_Invoice AS I ON I.BankAccountId = BA.FTOS_CB_BankAccountid
JOIN ebs.FTOS_TPM_InvoiceDetail AS ID ON ID.InvoiceId = I.FTOS_TPM_Invoiceid
JOIN ebs.FTOS_BNKAP_Application AS APP ON APP.ContractId = ID.ContractId 
JOIN ebs.FTOS_CB_Payment AS P ON I.FTOS_TPM_Invoiceid=P.InvoiceId
JOIN ebs.FTOS_BP_BankingProduct AS BP ON BP.FTOS_BP_BankingProductid=(SELECT CO.ProductId from ebs.FTOS_CB_Contract AS CO where CO.FTOS_CB_Contractid = ID.ContractId)
JOIN ebs.FTOS_TPM_PreInvoiceDetail AS PID ON PID.ContractId = C.FTOS_CB_Contractid
left JOIN ebs.FTOS_ONEY_ICE_Repayment as R ON P.PaymentNo = R.PaymentNo
left JOIN ebs.FTOS_ONEY_ICE_Repayment_Details AS RD ON RD.FTOS_ONEY_ICE_Repaymentid = R.FTOS_ONEY_ICE_Repaymentid -- detalii plati efectuate
left JOIN ebs.FTOS_ONEY_ICE_Libra_ReceivedPayments AS RP ON RP.FTOS_CB_Contractid = C.FTOS_CB_Contractid --and DATEPART(week, C.CreatedOn) = DATEPART(week, GETDATE()) take current week results

END

SELECT PID.*
FROM [ebs].[FTOS_TPM_PreInvoiceDetail] as PID
WHERE PID.Name = 'name'

核心思路是用条件聚合将多行佣金转为独立字段，同时解决原存储过程中因关联佣金表导致的重复行问题（原代码用DISTINCT处理重复，改用GROUP BY更高效且可控）：

1. 先确定佣金的区分标识：假设FTOS_TPM_PreInvoiceDetail表中通过Name字段区分不同类型的佣金（比如"佣金类型A"、"佣金类型B"），也可根据实际业务用Description等其他字段。
2. 替换原SELECT DISTINCT为GROUP BY，将所有非聚合字段放入GROUP BY子句。
3. 添加条件聚合字段提取不同佣金。

修改后的存储过程核心示例：

ALTER PROCEDURE [ebs].[DB_Task_ONEYCRPRaport]
AS
BEGIN
    SELECT
        CAST(A.ExternalMerchantId as varchar) AS "Merchant_guid",
        CAST(C.CreatedOn AS DATE) AS "Purchase_date",
        CAST(C.CreatedOn AS TIME) AS "Purchase_hour",
        C.ContractNo AS "Funding_reference",
        APP.OrderNo AS "External_reference",
        A.CustomerInternalId AS "Customer_external_code",
        CASE WHEN I.TotalAmountToPay = 0 THEN '-' ELSE '+' END AS "Total_amount_symbol",
        CASE WHEN I.TotalAmountToPay = 0 THEN I.TotalAmountToRecover ELSE I.TotalAmountToPay END AS "Total_amount",
        -- 提取第一类佣金（假设Name为'CommissionType1'）
        SUM(CASE WHEN PID.Name = 'CommissionType1' THEN PID.Amount ELSE 0 END) AS "Commission_1",
        -- 提取第二类佣金（假设Name为'CommissionType2'）
        SUM(CASE WHEN PID.Name = 'CommissionType2' THEN PID.Amount ELSE 0 END) AS "Commission_2"
    FROM ebs.Account as A
    -- 保留原所有关联逻辑，将佣金表的JOIN改为LEFT JOIN避免过滤无佣金的主记录
    JOIN ebs.FTOS_CB_Contract AS C ON A.Accountid = C.CustomerId
    JOIN ebs.FTOS_CB_BankAccount AS BA ON BA.FTOS_CB_BankAccountid = C.MainBankAccountId
    JOIN ebs.FTOS_CB_BankAccountOperation AS BAO ON BAO.BankAccountId = BA.FTOS_CB_BankAccountid
    JOIN Ebs.FTOS_CMB_Currency AS CU ON CU.FTOS_CMB_Currencyid = C.CurrencyId
    JOIN ebs.FTOS_TPM_Invoice AS I ON I.BankAccountId = BA.FTOS_CB_BankAccountid
    JOIN ebs.FTOS_TPM_InvoiceDetail AS ID ON ID.InvoiceId = I.FTOS_TPM_Invoiceid
    JOIN ebs.FTOS_BNKAP_Application AS APP ON APP.ContractId = ID.ContractId 
    JOIN ebs.FTOS_CB_Payment AS P ON I.FTOS_TPM_Invoiceid=P.InvoiceId
    JOIN ebs.FTOS_BP_BankingProduct AS BP ON BP.FTOS_BP_BankingProductid=(SELECT CO.ProductId from ebs.FTOS_CB_Contract AS CO where CO.FTOS_CB_Contractid = ID.ContractId)
    LEFT JOIN ebs.FTOS_TPM_PreInvoiceDetail AS PID ON PID.ContractId = C.FTOS_CB_Contractid
    LEFT JOIN ebs.FTOS_ONEY_ICE_Repayment as R ON P.PaymentNo = R.PaymentNo
    LEFT JOIN ebs.FTOS_ONEY_ICE_Repayment_Details AS RD ON RD.FTOS_ONEY_ICE_Repaymentid = R.FTOS_ONEY_ICE_Repaymentid
    LEFT JOIN ebs.FTOS_ONEY_ICE_Libra_ReceivedPayments AS RP ON RP.FTOS_CB_Contractid = C.FTOS_CB_Contractid
    -- 按所有非聚合字段分组
    GROUP BY
        A.ExternalMerchantId,
        CAST(C.CreatedOn AS DATE),
        CAST(C.CreatedOn AS TIME),
        C.ContractNo,
        APP.OrderNo,
        A.CustomerInternalId,
        CASE WHEN I.TotalAmountToPay = 0 THEN '-' ELSE '+' END,
        CASE WHEN I.TotalAmountToPay = 0 THEN I.TotalAmountToRecover ELSE I.TotalAmountToPay END
END

关键说明： #

• 将原JOIN ebs.FTOS_TPM_PreInvoiceDetail改为LEFT JOIN，避免因无佣金数据导致主表记录被过滤。
• 用SUM()聚合确保同一合同下的同类型佣金（如果有多行）自动求和；若每个合同每种佣金只有一行，用MAX()或MIN()效果一致。
• 替换DISTINCT为GROUP BY，从根源解决关联佣金表产生的重复行问题，性能更优。

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