SQL查询从表的每个记录生成多个记录 [英] SQL query to generate multiple records from every record of a table
本文介绍了SQL查询从表的每个记录生成多个记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我尝试使用VBA（从第一个表中选择数据，计算，然后插入第二个表），但是插入太慢，并且永远都会这样做。有人建议我直接在SQL中进行计算/插入，这就是我想要做的，但我对这种语言不太熟悉。
这里是VBA代码基本上是：
SQLStr =SELECT * FROM TABLE_PRETS
设置rs = cn.Execute（SQLStr）
如果没有（rs.EOF和rs.BOF）然后
rs.MoveFirst
直到rs.EOF = True
loanID = rs！N_CONTRAT
remainingBalance = rs！MONTANT_CREDIT
interestRate = rs！TAUX_ACTUEL / 100
insuranceRate = rs！TAUX_ASSURANCE
taxRate = rs！TVA
startDate = rs！DATE_DEBUT
monthlyRate = （利率*（1 + taxRate）+ insuranceRate）/ 12
对于i = 1到rs！DUREE
startingBalance = Round（remainingBalance，2）
principal = Round ！MENSUALITE - startingBalance * monthlyRate，2）
interestPaymentBT = Round（startingBalance * interestRate / 12，2）
taxOnIerest = round（interestPaymentBT * taxRate，2）
insurancePayment = Round（startingBalance * insuranceRate，2）
remainingBalance = Round（startingBalance - principal，2）
SQLStr =INSERT INTO TABLE_AMORTISSEMENT（N_CONTRAT，MOIS，DATE_ECHEANCE，MENSUALITE，SOLDE_DEPART，CAPITAL_AMORTI，_
& INTERET_HT，TVA，保证，CAPITAL_RESTANT_
& VALUES（& loanID&，& i，& DateAdd（m，i，startDate）&，& Replace（rs！MENSUALITE ，）&，_
&替换（startingBalance，，，。）&，&替换（principal，，，。） ，&替换（interestPaymentBT，，。）_
&，&替换（taxOnInterest，，，。）& ）&，_
&替换（remainingBalance，，，。）&）;
设置rs2 = cn.Execute（SQLStr，，adExecuteNoRecords）
下一个i
rs.MoveNext
循环
如果
这里是我在SQL中到目前为止的地方，但我很困惑：
DECLARE contracts_cursor CURSOR FOR
SELECT N_CONTRAT，DATE_DEBUT，DUREE，MONTANT_CREDIT，TAUX_ACTUEL / 100 AS TAUX_CREDIT，TAUX_ASSURANCE，TVA（TAUX_ACTUEL / 100 *（1 + TVA）+ TAUX_ASSURANCE） 12 AS TAUX_MENSUEL
FROM TABLE_PRETS
DECLARE @index INT，@startingBalance FLOAT，@principal FLOAT，@interestPaymentBT FLOAT，@taxOnInterest FLOAT，@insurancePayment FLOAT，@remainingBalance FLOAT
SET @index = 0;
OPEN contracts_cursor;
FETCH NEXT FROM contracts_cursor;
WHILE @@ FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM contracts_cursor;
- 我的大脑停止工作
END;
CLOSE contracts_cursor;
DEALLOCATE contracts_cursor;
GO
问题是，我不知道SQL是否可以计算一个整列（如Matlab），或者我必须循环使用列中的值。
第一个表的例子DATA：
来自第一个表数据的结果的实例（实际84行）：
解决方案
这可以使用递归CTE进行适当的设置。 p>
请参阅 http：// www。 sqlservercentral.com/articles/T-SQL/90955/ 为一些例子。
这应该让你关闭 - 我已经用了temp ables，但您可以重新格式化并将CTE的结果插入TABLE_AMORTISSEMENT。
使用您的示例贷款数据：
CREATE TABLE #TABLE_PRETS
（N_CONTRAT INT，
MONTANT_CREDIT DECIMAL（18,8），
TAUX_ACTUEL DECIMAL（18,8）
TAUX_ASSURANCE DECIMAL（18,8），
TVA DECIMAL（18,8），
DATE_DEBUT DATE，
MENSUALITE DECIMAL（18,8），
DUREE INT） ;
INSERT INTO #TABLE_PRETS VALUES（400083，300000，5.92997，0.0036，0.1，'2016-01-01'，4510.66，84）;
; WITH LOANS AS
（
SELECT
loanID = P.N_CONTRAT
，remainingBalance = P.MONTANT_CREDIT
， interestRate = P.TAUX_ACTUEL / 100
，insuranceRate = P.TAUX_ASSURANCE
，taxRate = P.TVA
，startDate = P.DATE_DEBUT
，monthlyRate =（（P.TAUX_ACTUEL / 100）*（1 +（P.TVA））+ P.TAUX_ASSURANCE）/ 12
，monthlypayment = P.MENSUALITE
，durationmonths = P.DUREE
- ，origstartingbalance = ROUND P.MONTANT_CREDIT，2）
FROM #TABLE_PRETS P
），
X AS（
SELECT
loanID = L.loanID
，monthnum = 1
，startingBalance = CONVERT（DECIMAL（18,8），ROUND（L.remainingBalance，2））
，principal = ROUND（L.monthlypayment - ROUND（L.remainingBalance，2）* L.monthlyRate， 2）
，interestPaymentBT = CONVERT（DECIMAL（ 18），ROUND（ROUND（L.remainingBalance，2）* L.interestRate / 12,2））
，taxOnInterest = ROUND（ROUND（ROUND（L.remainingBalance，2）* L.interestRate / 12 ，2）* L.taxRate，2）
，insurancePayment = CONVERT（DECIMAL（18,8），ROUND（ROUND（L.remainingBalance，2）* L.insuranceRate，2））
，remainingBalance = ROUND（ROUND（L.remainingBalance，2） - ROUND（L.monthlypayment - ROUND（L.remainingBalance，2）* L.monthlyRate，2），2）
FROM LOANS L
UNION ALL
SELECT
loanID = X.loanID
，monthnum = X.monthnum + 1
，startingBalance = CONVERT（DECIMAL（18,8），ROUND（X.remainingBalance，2））
，principal = ROUND（L.monthlypayment - ROUND（X.remainingBalance，2）* L.monthlyRate，2）
，interestPaymentBT = CONVERT（DECIMAL（18,8），ROUND（ROUND剩余平衡，2）* L.interestRate / 12，2））
，taxOnInterest = ROUND（ROUND（ROUND（X.remainingBalance，2）* L.interestRate / 12,2）* L.taxRate ，2）
，insurancePayment = CONVERT（DECIMAL（18,8），ROUND（ROUND（X.remainingBalance，2）* L.insuranceRate，2））
，remainingBalance = ROUND（ROUND剩余余额，2） - ROUND（L.monthlypayment - ROUND（X.remainingBalance，2）* L.monthlyRate，2），2）
FROM X
INNER JOIN LOANS L ON X.loanID = L. loanID
WHERE X.monthnum< L.durationmonths
）
SELECT * FROM X
I have a table with 3000 records, and using every single one of them, I have to generate around 200 records (600k records in total), and insert them in a second table through SQL Server 2012.
I tried doing this using VBA (Selecting data from first table, calculating, and then inserting in second table), but the insertion is too slow, and takes forever. Someone suggested that I do the calculations/insertion directly in SQL, and that's what I'm trying to do, but I'm not very familiar with the language.
Here's the VBA code basically :
SQLStr = "SELECT * FROM TABLE_PRETS"
Set rs = cn.Execute(SQLStr)
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
loanID = rs!N_CONTRAT
remainingBalance = rs!MONTANT_CREDIT
interestRate = rs!TAUX_ACTUEL / 100
insuranceRate = rs!TAUX_ASSURANCE
taxRate = rs!TVA
startDate = rs!DATE_DEBUT
monthlyRate = (interestRate * (1 + taxRate) + insuranceRate) / 12
For i = 1 To rs!DUREE
startingBalance = Round(remainingBalance, 2)
principal = Round(rs!MENSUALITE - startingBalance * monthlyRate, 2)
interestPaymentBT = Round(startingBalance * interestRate / 12, 2)
taxOnInterest = Round(interestPaymentBT * taxRate, 2)
insurancePayment = Round(startingBalance * insuranceRate, 2)
remainingBalance = Round(startingBalance - principal, 2)
SQLStr = "INSERT INTO TABLE_AMORTISSEMENT (N_CONTRAT,MOIS,DATE_ECHEANCE,MENSUALITE,SOLDE_DEPART,CAPITAL_AMORTI," _
& "INTERET_HT,TVA,ASSURANCE,CAPITAL_RESTANT)" _
& "VALUES (" & loanID & ", " & i & ", '" & DateAdd("m", i, startDate) & "', " & Replace(rs!MENSUALITE, ",", ".") & ", " _
& Replace(startingBalance, ",", ".") & ", " & Replace(principal, ",", ".") & ", " & Replace(interestPaymentBT, ",", ".") _
& ", " & Replace(taxOnInterest, ",", ".") & ", " & Replace(insurancePayment, ",", ".") & ", " _
& Replace(remainingBalance, ",", ".") & ");"
Set rs2 = cn.Execute(SQLStr, , adExecuteNoRecords)
Next i
rs.MoveNext
Loop
End if
Here's where I got so far in SQL, but I'm pretty stuck :
DECLARE contracts_cursor CURSOR FOR
SELECT N_CONTRAT, DATE_DEBUT, DUREE, MONTANT_CREDIT, TAUX_ACTUEL / 100 AS TAUX_CREDIT, TAUX_ASSURANCE, TVA, (TAUX_ACTUEL / 100 * (1 + TVA) + TAUX_ASSURANCE) / 12 AS TAUX_MENSUEL
FROM TABLE_PRETS
DECLARE @index INT, @startingBalance FLOAT, @principal FLOAT, @interestPaymentBT FLOAT, @taxOnInterest FLOAT, @insurancePayment FLOAT, @remainingBalance FLOAT
SET @index = 0;
OPEN contracts_cursor;
FETCH NEXT FROM contracts_cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM contracts_cursor;
--My brain stops working here
END;
CLOSE contracts_cursor;
DEALLOCATE contracts_cursor;
GO
The problem is, I don't know if SQL can calculate a whole column at once (like Matlab) or if I have to loop through values in a column.
Exemple of first table DATA :
Exemple of result from first table data (84 rows actually) :
解决方案
This can be done properly set-based with a recursive CTE.
See http://www.sqlservercentral.com/articles/T-SQL/90955/ for some examples.
This should get you close - I've used temp tables, but you can just re-format and insert the results of the CTE into TABLE_AMORTISSEMENT.
Using your example loan data:
CREATE TABLE #TABLE_PRETS
(N_CONTRAT INT,
MONTANT_CREDIT DECIMAL (18,8),
TAUX_ACTUEL DECIMAL (18,8),
TAUX_ASSURANCE DECIMAL (18,8),
TVA DECIMAL (18,8),
DATE_DEBUT DATE,
MENSUALITE DECIMAL (18,8),
DUREE INT);
INSERT INTO #TABLE_PRETS VALUES (400083, 300000, 5.92997, 0.0036, 0.1, '2016-01-01', 4510.66, 84);
;WITH LOANS AS
(
SELECT
loanID = P.N_CONTRAT
, remainingBalance = P.MONTANT_CREDIT
, interestRate = P.TAUX_ACTUEL / 100
, insuranceRate = P.TAUX_ASSURANCE
, taxRate = P.TVA
, startDate = P.DATE_DEBUT
, monthlyRate = ((P.TAUX_ACTUEL / 100) * (1 + (P.TVA)) + P.TAUX_ASSURANCE) / 12
, monthlypayment = P.MENSUALITE
, durationmonths = P.DUREE
--, origstartingbalance = ROUND(P.MONTANT_CREDIT,2)
FROM #TABLE_PRETS P
),
X AS (
SELECT
loanID = L.loanID
, monthnum = 1
, startingBalance = CONVERT(DECIMAL(18,8),ROUND(L.remainingBalance,2))
, principal = ROUND(L.monthlypayment - ROUND(L.remainingBalance,2) * L.monthlyRate, 2)
, interestPaymentBT = CONVERT(DECIMAL(18,8),ROUND(ROUND(L.remainingBalance,2) * L.interestRate / 12, 2))
, taxOnInterest = ROUND(ROUND(ROUND(L.remainingBalance,2) * L.interestRate / 12, 2) * L.taxRate, 2)
, insurancePayment = CONVERT(DECIMAL(18,8),ROUND(ROUND(L.remainingBalance,2) * L.insuranceRate, 2))
, remainingBalance = ROUND(ROUND(L.remainingBalance,2) - ROUND(L.monthlypayment - ROUND(L.remainingBalance,2) * L.monthlyRate, 2), 2)
FROM LOANS L
UNION ALL
SELECT
loanID = X.loanID
, monthnum = X.monthnum + 1
, startingBalance = CONVERT(DECIMAL(18,8),ROUND(X.remainingBalance,2) )
, principal = ROUND(L.monthlypayment - ROUND(X.remainingBalance,2) * L.monthlyRate, 2)
, interestPaymentBT = CONVERT(DECIMAL(18,8),ROUND(ROUND(X.remainingBalance,2) * L.interestRate / 12, 2))
, taxOnInterest = ROUND(ROUND(ROUND(X.remainingBalance,2) * L.interestRate / 12, 2) * L.taxRate, 2)
, insurancePayment = CONVERT(DECIMAL(18,8),ROUND(ROUND(X.remainingBalance,2) * L.insuranceRate, 2))
, remainingBalance = ROUND(ROUND(X.remainingBalance,2) - ROUND(L.monthlypayment - ROUND(X.remainingBalance,2) * L.monthlyRate, 2), 2)
FROM X
INNER JOIN LOANS L ON X.loanID = L.loanID
WHERE X.monthnum < L.durationmonths
)
SELECT * FROM X
这篇关于SQL查询从表的每个记录生成多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
