基于在SQL Server中返回的查询的更新表
问题描述:
我遇到了更新查询。我有一个查询为基于在SQL Server中返回的查询的更新表
SELECT s_no, loan_id,repayment_date,principal,loan_balance, count(*) as repeatTimes
FROM loan_repayment_plan_mcg
GROUP BY s_no, loan_id, repayment_date,principal,loan_balance
HAVING count(*) > 1
它返回的输出:
s_no loan_id repayment_date principal loan_balance repeatTimes
1 21111 2012-03-13 0.00 5000.00 2
2 21311 2012-04-12 0.00 2000.00 2
3 21111 2012-05-13 500 5000.00 2
4 21111 2012-06-14 0.00 5000.00 3
我想更新loan_balance
从我上面的选择查询乘以repeatTimes
基于loan_id
和repayment_date
它结合在一起,使一个唯一的行。
答
的传统方法,使用UPDATE从JOIN
update A
set loan_balance = loan_balance * repeatTimes
from loan_repayment_plan_mcg A
join
(
SELECT s_no, loan_id,repayment_date,principal,loan_balance, count(*) as repeatTimes
FROM loan_repayment_plan_mcg
GROUP BY s_no, loan_id, repayment_date,principal,loan_balance
HAVING count(*) > 1
) B on A.s_no = B.s_no
and A.loan_id = B.loan_id
and A.repayment_date = B.repayment_date
and A.principal = B.principal
and A.loan_balance = B.loan_balance;
在SQL Server中使用窗函数和CTE 2008
;with cte as (
SELECT *,sum(loan_balance) over (
partition by s_no,loan_id,repayment_date,principal,loan_balance) total_balance
FROM loan_repayment_plan_mcg
)
update cte
set loan_balance = total_balance
where loan_balance != total_balance;
+0
谢谢,我试过你第一次查询只是稍作修改'set loan_balance = a.loan_balance * repeatTimes' – 2013-05-03 04:53:22
请考虑一下,你违反了3NF本 - 以及您需要可能的原因(主要是性能问题) – Najzero 2013-05-02 11:12:54
@Najzero这些都是旧系统的问题,我想在数据重复时纠正它。 – 2013-05-02 11:15:29