如何在存储过程中用游标汇总贷款额？附无游标替代方案

嘿，我来帮你搞定这个问题！首先咱们先明确你的核心需求，再一步步解决：

核心需求回顾 #

你有两张表：

• General表：包含唯一的id和对应的name字段
• loan表：包含id（同一客户可重复）和loan amount字段
• 目标：输出每个id对应的name，以及该id的贷款总额，要求每个id只出现一次；同时你想知道是否可以不用游标实现——答案是肯定的，游标完全没必要，用分组聚合+关联查询就能高效解决。

为什么你的当前查询会出现id重复？ #

你现在的SQL直接关联了多个带重复记录的表（比如BM_RLOS_ExistingBMLiabilitiesGrid这类Grid表），而且没有对这些多记录的表做分组聚合，导致主表的行被重复输出，最终出现id重复的情况。

不用游标的最优方案：分组聚合+JOIN #

先对loan表按id分组求和，再和General表关联，就能得到每个id的唯一记录：

SELECT 
    g.id,
    g.name,
    -- 用ISNULL确保没有贷款的id显示0
    ISNULL(SUM(l.loan_amount), 0) AS total_loan_amount
FROM 
    General g
-- LEFT JOIN保证所有General表的id都能被查到，哪怕没有贷款
LEFT JOIN 
    loan l ON g.id = l.id
-- 按General表的唯一字段分组，确保每个id只出一次
GROUP BY 
    g.id, g.name;

如果只需要展示有贷款记录的id，把LEFT JOIN换成INNER JOIN即可。

针对你现有SQL的修改建议 #

你的现有查询涉及多个业务表，重复的根源是直接关联了多对多的Grid表。要解决这个问题，你需要先对这些产生重复的子表做分组聚合，再和主表关联，避免直接关联带重复记录的表。

举个简化的修改示例，针对你SQL里的贷款总额和计数部分：

SELECT 
    A.bpm_referenceno,
    -- 保留你原来的CASE逻辑处理其他字段...
    CASE WHEN A.branch='' OR A.branch IS NULL OR A.branch='null' THEN '' ELSE A.branch END AS branch,
    CASE WHEN A.originator='' OR A.originator IS NULL OR A.originator='null' THEN '' ELSE A.originator END AS originator,
    -- 用分组后的子查询获取聚合后的贷款额
    ISNULL(E_agg.total_loanamount, '0') AS loanamounttxndetails,
    ISNULL(E_agg.total_customerdbr, '0.00') AS customerdbrtxndetails,
    -- 用分组后的子查询获取计数
    ISNULL(F_agg.mycount, 0) AS stlment_count,
    -- 其他字段保持你的原有逻辑...
    CASE WHEN G.isselected='true' THEN G.insuranceType ELSE '' END AS insuranceType,
    CASE WHEN D.calldescription='MG Contract Creation' AND D.callstatus='SUCCESS' THEN D.callreferenceid ELSE '' END AS callreferenceid
FROM 
    BM_RLOS_EXTTABLE A WITH (NOLOCK)
INNER JOIN 
    BM_RLOS_BasicLoanDetailsForm B WITH (NOLOCK) ON A.bpm_referenceno = B.bpm_referenceno
INNER JOIN 
    BM_RLOS_DisbursementCallsGrid D WITH (NOLOCK) ON A.bpm_referenceno = D.bpm_referenceno
-- 替换原E表关联：先对E表按bpm_referenceno分组聚合
INNER JOIN (
    SELECT 
        bpm_referenceno,
        -- 聚合贷款总额，先处理空值再求和
        SUM(CASE 
            WHEN loanamounttxndetails IS NULL OR loanamounttxndetails IN ('', 'null') THEN 0 
            ELSE CAST(loanamounttxndetails AS DECIMAL) 
        END) AS total_loanamount,
        -- 聚合dbr值
        SUM(CASE 
            WHEN customerdbrtxndetails IS NULL OR customerdbrtxndetails IN ('', 'null') THEN 0 
            ELSE CAST(customerdbrtxndetails AS DECIMAL) 
        END) AS total_customerdbr
    FROM BM_RLOS_CheckFinalEligibilityForm
    GROUP BY bpm_referenceno
) E_agg ON A.bpm_referenceno = E_agg.bpm_referenceno
-- 替换原F表关联：先对F表按bpm_referenceno分组计数
INNER JOIN (
    SELECT 
        bpm_referenceno,
        COUNT(*) AS mycount
    FROM BM_RLOS_ExistingBMLiabilitiesGrid
    GROUP BY bpm_referenceno
) F_agg ON A.bpm_referenceno = F_agg.bpm_referenceno
-- 其他关联表如果也存在重复记录，同样需要先做分组处理
INNER JOIN BM_RLOS_InsuranceProductSelectionGrid G WITH (NOLOCK) ON A.bpm_referenceno = G.bpm_referenceno
INNER JOIN BM_RLOS_ChargeAndFeeDetailsForm H WITH (NOLOCK) ON A.bpm_referenceno = H.bpm_referenceno
INNER JOIN BM_RLOS_DecisionHistoryForm I WITH (NOLOCK) ON A.bpm_referenceno = I.bpm_referenceno
INNER JOIN wfcurrentroutelogtable J WITH (NOLOCK) ON A.bpm_referenceno = J.ProcessInstanceId

通过这种方式，先把每个多记录的子表聚合为单记录，再关联主表，就能确保每个bpm_referenceno只出现一次，解决重复问题。

关于游标的说明 #

完全不需要用游标来实现这个需求！游标在处理这类聚合查询时效率极低，尤其是数据量大的时候，数据库对分组聚合+JOIN有原生的优化，性能远优于游标方案，代码也更简洁易维护。

内容的提问来源于stack exchange，提问作者Md Kamran Azam