如何对游标值求和并在同一过程中将值存储在另一个 select 语句中?

Question

我有两个表，一个是通用表，另一个是loan表。

General表包括id和name，id不重复。

Loan表包括id和loan amount。在loan表中，id可能会重复，因为一个客户可以有多个贷款。

想法是创建一个过程，该过程将在id的基础上连接此表，并在光标的帮助下显示带有该特定id的所有贷款id、name和总和的最终表(必须汇总共同id的值并存储在单列中(。

只能使用过程和游标。

如果可以在没有光标的情况下解决，那么请提供替代解决方案。

我使用相同的查询，但不幸的是最终结果多次显示id：

SELECT
A.bpm_referenceno,
CASE WHEN B.loanbookingbranch='--Select--' or B.loanbookingbranch='null' THEN '' ELSE B.loanbookingbranch END,
CASE WHEN A.branch='' OR A.branch IS NULL OR A.branch='null' THEN '' ELSE A.branch END,
'',
CASE WHEN A.originator='' OR A.originator IS NULL OR A.originator='null' THEN '' ELSE A.originator END,
A.cif_id,
CASE WHEN D.callexecutiondate='' OR D.callexecutiondate IS NULL OR D.callexecutiondate ='null' THEN '' ELSE CASE WHEN D.calldescription='MG Contract Creation' AND D.callstatus='SUCCESS' THEN D.callexecutiondate END END,
CASE WHEN A.originationdate IS NULL THEN '' ELSE A.originationdate END,
CASE WHEN A.request_type='' OR A.request_type IS NULL OR A.request_type='null' THEN '' ELSE A.request_type END,
CASE WHEN a.loan_subtype='' OR a.loan_subtype IS NULL OR a.loan_subtype='null' THEN '' ELSE a.loan_subtype END,
CASE WHEN E.loanamounttxndetails IS NULL OR E.loanamounttxndetails ='null' OR E.loanamounttxndetails='' THEN '0' ELSE e.loanamounttxndetails END,
CASE WHEN E.customerdbrtxndetails IS NULL OR E.customerdbrtxndetails ='null' OR E.customerdbrtxndetails='' THEN '0.00' ELSE E.customerdbrtxndetails END,
'stlment count',
'loan os',
--Here I am inserting the query--
(SELECT
isnull(ab.mycount,0)
FROM
bm_rlos_exttable aa
LEFT OUTER JOIN
(SELECT bpm_referenceno,count(bpm_referenceno) mycount FROM
BM_RLOS_ExistingBMLiabilitiesGrid GROUP BY bpm_referenceno) ab ON aa.bpm_referenceno = ab.bpm_referenceno),
CASE WHEN G.isselected='true' THEN G.insuranceType ELSE '' END,
CASE WHEN E.loantenortxndetails IS NULL OR E.loantenortxndetails='' OR E.loantenortxndetails='null' THEN '' ELSE E.loantenortxndetails END,
CASE WHEN E.interestratetxndetails IS NULL OR E.interestratetxndetails='' OR E.interestratetxndetails='null' THEN '' ELSE E.interestratetxndetails END,
'CHARGE1',
'CHARGE2',
CASE WHEN D.calldescription='MG Contract Creation' AND D.callstatus='SUCCESS' THEN D.callreferenceid ELSE '' END,
'rco',
'tat'
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
INNER JOIN BM_RLOS_CheckFinalEligibilityForm E WITH (NOLOCK)
ON A.bpm_referenceno = E.bpm_referenceno
INNER JOIN BM_RLOS_ExistingBMLiabilitiesGrid F WITH (NOLOCK)
ON A.bpm_referenceno = F.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

Answer 1

这应该可以解决问题

SELECT
a.id,
a.name,
b.mysum
FROM
General_Table a 
LEFT OUTER JOIN (select bpm_id, SUM(outstandingamount) mysum 
from loan_table group by bpm_id) b
ON a.id = b.bpm_id

编辑：子查询已更改(根据您的加入子句，我假设您在loan_table中的 id 字段bpm_id(

编辑2：
请注意，为了完成这项工作，我将嵌套查询移动到新的左连接中。 如果嵌套查询返回 1 条以上的记录，则不能在 select 语句中使用嵌套查询

SELECT
A.bpm_referenceno,
CASE WHEN B.loanbookingbranch='--Select--' or B.loanbookingbranch='null' THEN '' ELSE B.loanbookingbranch END,
CASE WHEN A.branch='' OR A.branch IS NULL OR A.branch='null' THEN '' ELSE A.branch END,
'',
CASE WHEN A.originator='' OR A.originator IS NULL OR A.originator='null' THEN '' ELSE A.originator END,
A.cif_id,
CASE WHEN D.callexecutiondate='' OR D.callexecutiondate IS NULL OR D.callexecutiondate ='null' THEN '' ELSE CASE WHEN D.calldescription='MG Contract Creation' AND D.callstatus='SUCCESS' THEN D.callexecutiondate END END,
CASE WHEN A.originationdate IS NULL THEN '' ELSE A.originationdate END,
CASE WHEN A.request_type='' OR A.request_type IS NULL OR A.request_type='null' THEN '' ELSE A.request_type END,
CASE WHEN a.loan_subtype='' OR a.loan_subtype IS NULL OR a.loan_subtype='null' THEN '' ELSE a.loan_subtype END,
CASE WHEN E.loanamounttxndetails IS NULL OR E.loanamounttxndetails ='null' OR E.loanamounttxndetails='' THEN '0' ELSE e.loanamounttxndetails END,
CASE WHEN E.customerdbrtxndetails IS NULL OR E.customerdbrtxndetails ='null' OR E.customerdbrtxndetails='' THEN '0.00' ELSE E.customerdbrtxndetails END,
'stlment count',
'loan os',
--Here I am inserting the query--
mycount,
CASE WHEN G.isselected='true' THEN G.insuranceType ELSE '' END,
CASE WHEN E.loantenortxndetails IS NULL OR E.loantenortxndetails='' OR E.loantenortxndetails='null' THEN '' ELSE E.loantenortxndetails END,
CASE WHEN E.interestratetxndetails IS NULL OR E.interestratetxndetails='' OR E.interestratetxndetails='null' THEN '' ELSE E.interestratetxndetails END,
'CHARGE1',
'CHARGE2',
CASE WHEN D.calldescription='MG Contract Creation' AND D.callstatus='SUCCESS' THEN D.callreferenceid ELSE '' END,
'rco',
'tat'
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
INNER JOIN BM_RLOS_CheckFinalEligibilityForm E WITH (NOLOCK)
ON A.bpm_referenceno = E.bpm_referenceno
INNER JOIN BM_RLOS_ExistingBMLiabilitiesGrid F WITH (NOLOCK)
ON A.bpm_referenceno = F.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
left join
(SELECT bpm_referenceno,count(bpm_referenceno) mycount FROM
BM_RLOS_ExistingBMLiabilitiesGrid GROUP BY bpm_referenceno) ab ON ab.bpm_referenceno = a.bpm_referenceno)

Answer 2

你根本不需要光标！效率低下。这可以通过JOIN和GROUP来完成。请尝试以下查询：

select g.id, l.totalLoan from GeneralTable [g]
join (
select id, sum(loan) [totalLoan] from LoanTable
group by id
) [l] on [g].id = [l].id