Sql server通过每个记录和编辑循环 [英] Sql server looping thru each records and editing

问题描述

如何编写与vba代码等效的SQL代码（如下所示）。在此先感谢任何帮助。我没有做太多的SQL编码。



我在MS Access中编写了这段代码来编辑单个表中的记录。这两个记录集用于测试J_CaseNum或J_Index的值是否有变化。



对于每个组合，J_PersonIndex需要从1开始递增。对于给定的组合，可以有1到70人，表中有160,000条记录。



一旦代码运行，结果应如下所示：

How do i write SQL code that's equivalent to vba code (below). Thanks in advance for any assistance on this. I haven't done much SQL coding.

I've written this code in MS Access to edit records in a single table. The two recordset is used to to test if there is a change in the values of J_CaseNum or J_Index.

For each combination the J_PersonIndex needs to be incremented starting at 1. There can be anywhere from 1 to 70 person for a given combination, 160,000 records in the table.

Once the code is ran the results should look like this:

CaseNum    Index  Name   PersonIndex
1001       001    Allen  01
1001       001    Brown  02
1001       001    Smith  03
1001       002    Alfred 01
1002       001    Billy  01
1002       001    Lima   02

这是VBA代码

Here's the VBA code

sql = "Select J_CaseNum, J_Index, J_VehicleIndex, J_PersonIndex, J_CaseNo, J_PersonID, Ind_Last_NM  from dbo_DCIPS Order By J_CaseNum, J_Index, Ind_Last_NM"
Set rs = CurrentDb.OpenRecordset(sql)
Set rs1 = CurrentDb.OpenRecordset(sql)

rs.MoveLast
rs.MoveFirst
i = 0

While Not rs.EOF
    If i = 0 Then
        rs.Edit
        rs!J_PersonIndex = "01"
        rs!J_CaseNo = rs!J_CaseNum & rs!J_Index
        rs!J_PersonID = rs!J_CaseNo & rs!J_VehicleIndex & rs!J_PersonIndex
        rs.Update
        j = 1
        i = 1
        rs.MoveNext
    Else
        If (rs1!J_CaseNum = rs!J_CaseNum) And (rs1!J_Index = rs!J_Index) Then
            j = j + 1
            s = j
            rs.Edit
            rs!J_PersonIndex = Right("00" + s, 2)
            rs!J_CaseNo = rs!J_CaseNum & rs!J_Index
            rs!J_PersonID = rs!J_CaseNo & rs!J_VehicleIndex & rs!J_PersonIndex
            rs.Update
            rs.MoveNext
            rs1.MoveNext
        Else
            j = 1
            s = j
            rs.Edit
            rs!J_PersonIndex = Right("00" + s, 2)
            rs!J_CaseNo = rs!J_CaseNum & rs!J_Index
            rs!J_PersonID = rs!J_CaseNo & rs!J_VehicleIndex & rs!J_PersonIndex
            rs.Update
            rs.MoveNext
            rs1.MoveNext
        End If
    End If
    i = i + 1
Wend

我尝试了什么：



VBA代码运行正常，需要7小时才能完成。

What I have tried:

The VBA code runs fine, took 7hours to finish.

推荐答案

由于我没有表结构，也没有要测试的数据，所以我不能完全确定结果。但是，如果您至少将SQL Server 2005作为RDBMS，则必须这样做。

Since I haven't the table structure, and no data to test, I'm not absolutely sure about the result. But this should have to work if you have at least SQL Server 2005 as RDBMS.

WITH CTE_DCIPS
AS (
SELECT ROW_NUMBER() OVER(PARTITION BY J_CaseNum, J_Index ORDER BY Ind_Last_NM) RowNum 
    , J_CaseNum
    , J_Index
    , J_VehicleIndex
    , J_PersonIndex
    , J_CaseNo
    , J_PersonID
    , Ind_Last_NM  
FROM dbo_DCIPS 
)

UPDATE tbl
SET tbl.J_PersonIndex = RIGHT('00' + CONVERT(varchar(5), cte.RowNum), 2)
    , tbl.J_CaseNo = tbl.J_CaseNum + tbl.J_Index
    , tbl.J_PersonID = tbl.J_CaseNo + tbl.J_VehicleIndex + RIGHT('00' + CONVERT(varchar(5), cte.RowNum), 2)
FROM dbo_DCIPS tbl 
INNER JOIN CTE_DCIPS cte
ON tbl.J_CaseNum = cte.J_CaseNum AND tbl.J_Index = cte.J_Index

这篇关于Sql server通过每个记录和编辑循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！