I have (simplified for the example) a table with the following data
我(在示例中简化了)包含以下数据的表
Row Start Finish ID Amount
--- --------- ---------- -- ------
1 2008-10-01 2008-10-02 01 10
2 2008-10-02 2008-10-03 02 20
3 2008-10-03 2008-10-04 01 38
4 2008-10-04 2008-10-05 01 23
5 2008-10-05 2008-10-06 03 14
6 2008-10-06 2008-10-07 02 3
7 2008-10-07 2008-10-08 02 8
8 2008-10-08 2008-11-08 03 19
The dates represent a period in time, the ID is the state a system was in during that period and the amount is a value related to that state.
日期表示一段时间,ID是系统在该时间段内所处的状态,金额是与该状态相关的值。
What I want to do is to aggregate the Amounts for adjacent rows with the same ID number, but keep the same overall sequence so that contiguous runs can be combined. Thus I want to end up with data like:
我想要做的是聚合具有相同ID号的相邻行的Amounts,但保持相同的整体序列,以便可以组合连续的运行。因此,我希望得到如下数据:
Row Start Finish ID Amount
--- --------- ---------- -- ------
1 2008-10-01 2008-10-02 01 10
2 2008-10-02 2008-10-03 02 20
3 2008-10-03 2008-10-05 01 61
4 2008-10-05 2008-10-06 03 14
5 2008-10-06 2008-10-08 02 11
6 2008-10-08 2008-11-08 03 19
I am after a T-SQL solution that can be put into a SP, however I can't see how to do that with simple queries. I suspect that it may require iteration of some sort but I don't want to go down that path.
我正在使用可以放入SP的T-SQL解决方案,但是我无法通过简单查询看到如何做到这一点。我怀疑它可能需要某种迭代,但我不想走那条路。
The reason I want to do this aggregation is that the next step in the process is to do a SUM() and Count() grouped by the unique ID's that occur within the sequence, so that my final data will look something like:
我想要进行这种聚合的原因是该过程的下一步是按照序列中出现的唯一ID进行SUM()和Count(),这样我的最终数据将如下所示:
ID Counts Total
-- ------ -----
01 2 71
02 2 31
03 2 33
However if I do a simple
但是,如果我做一个简单的
SELECT COUNT(ID), SUM(Amount) FROM data GROUP BY ID
On the original table I get something like
在原始的桌子上,我得到了类似的东西
ID Counts Total
-- ------ -----
01 3 71
02 3 31
03 2 33
Which is not what I want.
这不是我想要的。
4 个解决方案
#1
3
If you read the book "Developing Time-Oriented Database Applications in SQL" by R T Snodgrass (the pdf of which is available from his web site under publications), and get as far as Figure 6.25 on p165-166, you will find the non-trivial SQL which can be used in the current example to group the various rows with the same ID value and continuous time intervals.
如果您阅读RT Snodgrass撰写的“在SQL中开发面向时间的数据库应用程序”一书(其pdf可从他的网站上的出版物中获得),并在p165-166上得到图6.25,您会发现非-trivial SQL,可以在当前示例中用于对具有相同ID值和连续时间间隔的各行进行分组。
The query development below is close to correct, but there is a problem spotted right at the end, that has its source in the first SELECT statement. I've not yet tracked down why the incorrect answer is being given. [If someone can test the SQL on their DBMS and tell me whether the first query works correctly there, it would be a great help!]
下面的查询开发接近正确,但最后发现了一个问题,它的源代码在第一个SELECT语句中。我还没有找到为什么给出错误的答案。 [如果有人可以在他们的DBMS上测试SQL并告诉我第一个查询是否在那里正常工作,那将是一个很大的帮助!]
It looks something like:
它看起来像:
-- Derived from Figure 6.25 from Snodgrass "Developing Time-Oriented
-- Database Applications in SQL"
CREATE TABLE Data
(
Start DATE,
Finish DATE,
ID CHAR(2),
Amount INT
);
INSERT INTO Data VALUES('2008-10-01', '2008-10-02', '01', 10);
INSERT INTO Data VALUES('2008-10-02', '2008-10-03', '02', 20);
INSERT INTO Data VALUES('2008-10-03', '2008-10-04', '01', 38);
INSERT INTO Data VALUES('2008-10-04', '2008-10-05', '01', 23);
INSERT INTO Data VALUES('2008-10-05', '2008-10-06', '03', 14);
INSERT INTO Data VALUES('2008-10-06', '2008-10-07', '02', 3);
INSERT INTO Data VALUES('2008-10-07', '2008-10-08', '02', 8);
INSERT INTO Data VALUES('2008-10-08', '2008-11-08', '03', 19);
SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS (SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS (SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS (SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish)
OR (T2.Start <= L.Finish AND L.Finish < T2.Finish)));
The output from that query is:
该查询的输出是:
01 2008-10-01 2008-10-02
01 2008-10-03 2008-10-05
02 2008-10-02 2008-10-03
02 2008-10-06 2008-10-08
03 2008-10-05 2008-10-06
03 2008-10-05 2008-11-08
03 2008-10-08 2008-11-08
Edited: There's a problem with the penultimate row - it should not be there. And I'm not clear (yet) where it is coming from.
编辑:倒数第二行有一个问题 - 它应该不存在。而且我还不清楚(它)来自哪里。
Now we need to treat that complex expression as a query expression in the FROM clause of another SELECT statement, which will sum the amount values for a given ID over the entries that overlap with the maximal ranges shown above.
现在,我们需要将该复杂表达式视为另一个SELECT语句的FROM子句中的查询表达式,该语句将对与上面显示的最大范围重叠的条目求和给定ID的金额值。
SELECT M.ID, M.Start, M.Finish, SUM(D.Amount)
FROM Data AS D,
(SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS (SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS (SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS (SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish)
OR (T2.Start <= L.Finish AND L.Finish < T2.Finish)))) AS M
WHERE D.ID = M.ID
AND M.Start <= D.Start
AND M.Finish >= D.Finish
GROUP BY M.ID, M.Start, M.Finish
ORDER BY M.ID, M.Start;
This gives:
ID Start Finish Amount
01 2008-10-01 2008-10-02 10
01 2008-10-03 2008-10-05 61
02 2008-10-02 2008-10-03 20
02 2008-10-06 2008-10-08 11
03 2008-10-05 2008-10-06 14
03 2008-10-05 2008-11-08 33 -- Here be trouble!
03 2008-10-08 2008-11-08 19
Edited: This is almost the correct data set on which to do the COUNT and SUM aggregation requested by the original question, so the final answer is:
编辑:这几乎是正确的数据集,用于执行原始问题所请求的COUNT和SUM聚合,因此最终答案是:
SELECT I.ID, COUNT(*) AS Number, SUM(I.Amount) AS Amount
FROM (SELECT M.ID, M.Start, M.Finish, SUM(D.Amount) AS Amount
FROM Data AS D,
(SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS
(SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS
(SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS
(SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish) OR
(T2.Start <= L.Finish AND L.Finish < T2.Finish)))
) AS M
WHERE D.ID = M.ID
AND M.Start <= D.Start
AND M.Finish >= D.Finish
GROUP BY M.ID, M.Start, M.Finish
) AS I
GROUP BY I.ID
ORDER BY I.ID;
id number amount
01 2 71
02 2 31
03 3 66
Review: Oh! Drat...the entry for 3 has twice the 'amount' that it should have. Previous 'edited' parts indicate where things started to go wrong. It looks as though either the first query is subtly wrong (maybe it is intended for a different question), or the optimizer I'm working with is misbehaving. Nevertheless, there should be an answer closely related to this that will give the correct values.
评论:哦! Drat ... 3的条目有两倍于它应该具有的'金额'。之前的“已编辑”部分表示事情开始出错的地方。看起来好像第一个查询是巧妙的错误(可能是针对不同的问题),或者我正在使用的优化器是行为不端。然而,应该有一个与此密切相关的答案,它将给出正确的值。
For the record: tested on IBM Informix Dynamic Server 11.50 on Solaris 10. However, should work fine on any other moderately standard-conformant SQL DBMS.
对于记录:在Solaris 10上的IBM Informix Dynamic Server 11.50上进行了测试。但是,应该可以在任何其他符合标准的标准SQL DBMS上正常工作。
#2
1
Probably need to create a cursor and loop through the results, keeping track of which id you are working with and accumulating the data along the way. When the id changes you can insert the accumulated data into a temporary table and return the table at the end of the procedure (select all from it). A table-based function might be better as you can then just insert into the return table as you go along.
可能需要创建一个游标并循环遍历结果,跟踪您正在使用的ID以及沿途累积数据。当id更改时,您可以将累积的数据插入临时表,并在过程结束时返回表(从中选择全部)。基于表的函数可能会更好,因为您可以随时插入返回表。
#3
1
I suspect that it may require iteration of some sort but I don't want to go down that path.
我怀疑它可能需要某种迭代,但我不想走那条路。
I think that's the route you'll have to take, use a cursor to populate a table variable. If you have a large number of records you could use a permanent table to store the results then when you need to retrieve the data you could process only the new data.
我认为这是你必须采取的路线,使用光标填充表变量。如果您有大量记录,则可以使用永久表来存储结果,然后当您需要检索数据时,您只能处理新数据。
I would add a bit field with a default of 0 to the source table to keep track of which records have been processed. Assuming no one is using select * on the table, adding a column with a default value won't affect the rest of your application.
我将向源表添加一个默认值为0的位字段,以跟踪已处理的记录。假设没有人在表上使用select *,添加具有默认值的列将不会影响应用程序的其余部分。
Add a comment to this post if you want help coding the solution.
如果您需要帮助编写解决方案,请在此帖子中添加评论。
#4
0
Well I decided to go down the iteration route using a mixture of joins and cursors. By JOINing the data table against itself I can create a link list of only those records that are consecutive.
好吧,我决定使用连接和游标的混合来沿着迭代路线前进。通过将数据表连接到自身,我可以创建仅包含连续记录的链接列表。
INSERT INTO #CONSEC
SELECT a.ID, a.Start, b.Finish, b.Amount
FROM Data a JOIN Data b
ON (a.Finish = b.Start) AND (a.ID = b.ID)
Then I can unwind the list by iterating over it with a cursor, and doing updates back to the data table to adjust (And delete the now extraneous records from the Data table)
然后我可以通过使用游标迭代它来展开列表,并将更新返回到数据表进行调整(并从Data表中删除现在无关的记录)
DECLARE CCursor CURSOR FOR
SELECT ID, Start, Finish, Amount FROM #CONSEC ORDER BY Start DESC
@Total = 0
OPEN CCursor
FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
WHILE @FETCH_STATUS = 0
BEGIN
@Total = @Total + @Amount
@Start_Last = @Start
@Finish_Last = @Finish
@ID_Last = @ID
DELETE FROM Data WHERE Start = @Finish
FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
IF (@ID_Last<> @ID) OR (@Finish<>@Start_Last)
BEGIN
UPDATE Data
SET Amount = Amount + @Total
WHERE Start = @Start_Last
@Total = 0
END
END
CLOSE CCursor
DEALLOCATE CCursor
This all works and has acceptable performance for typical data that I am using.
这一切都有效,并且对于我正在使用的典型数据具有可接受的性能。
I did find one small issue with the above code. Originally I was updating the Data table on each loop through the cursor. But this didn't work. It seems that you can only do one update on a record, and that multiple updates (in order to keep adding data) revert back to the reading the original contents of the record.
我确实在上面的代码中发现了一个小问题。最初我是通过游标更新每个循环上的Data表。但这没效果。您似乎只能对记录执行一次更新,并且多次更新(为了保持添加数据)还原为读取记录的原始内容。
#1
3
If you read the book "Developing Time-Oriented Database Applications in SQL" by R T Snodgrass (the pdf of which is available from his web site under publications), and get as far as Figure 6.25 on p165-166, you will find the non-trivial SQL which can be used in the current example to group the various rows with the same ID value and continuous time intervals.
如果您阅读RT Snodgrass撰写的“在SQL中开发面向时间的数据库应用程序”一书(其pdf可从他的网站上的出版物中获得),并在p165-166上得到图6.25,您会发现非-trivial SQL,可以在当前示例中用于对具有相同ID值和连续时间间隔的各行进行分组。
The query development below is close to correct, but there is a problem spotted right at the end, that has its source in the first SELECT statement. I've not yet tracked down why the incorrect answer is being given. [If someone can test the SQL on their DBMS and tell me whether the first query works correctly there, it would be a great help!]
下面的查询开发接近正确,但最后发现了一个问题,它的源代码在第一个SELECT语句中。我还没有找到为什么给出错误的答案。 [如果有人可以在他们的DBMS上测试SQL并告诉我第一个查询是否在那里正常工作,那将是一个很大的帮助!]
It looks something like:
它看起来像:
-- Derived from Figure 6.25 from Snodgrass "Developing Time-Oriented
-- Database Applications in SQL"
CREATE TABLE Data
(
Start DATE,
Finish DATE,
ID CHAR(2),
Amount INT
);
INSERT INTO Data VALUES('2008-10-01', '2008-10-02', '01', 10);
INSERT INTO Data VALUES('2008-10-02', '2008-10-03', '02', 20);
INSERT INTO Data VALUES('2008-10-03', '2008-10-04', '01', 38);
INSERT INTO Data VALUES('2008-10-04', '2008-10-05', '01', 23);
INSERT INTO Data VALUES('2008-10-05', '2008-10-06', '03', 14);
INSERT INTO Data VALUES('2008-10-06', '2008-10-07', '02', 3);
INSERT INTO Data VALUES('2008-10-07', '2008-10-08', '02', 8);
INSERT INTO Data VALUES('2008-10-08', '2008-11-08', '03', 19);
SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS (SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS (SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS (SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish)
OR (T2.Start <= L.Finish AND L.Finish < T2.Finish)));
The output from that query is:
该查询的输出是:
01 2008-10-01 2008-10-02
01 2008-10-03 2008-10-05
02 2008-10-02 2008-10-03
02 2008-10-06 2008-10-08
03 2008-10-05 2008-10-06
03 2008-10-05 2008-11-08
03 2008-10-08 2008-11-08
Edited: There's a problem with the penultimate row - it should not be there. And I'm not clear (yet) where it is coming from.
编辑:倒数第二行有一个问题 - 它应该不存在。而且我还不清楚(它)来自哪里。
Now we need to treat that complex expression as a query expression in the FROM clause of another SELECT statement, which will sum the amount values for a given ID over the entries that overlap with the maximal ranges shown above.
现在,我们需要将该复杂表达式视为另一个SELECT语句的FROM子句中的查询表达式,该语句将对与上面显示的最大范围重叠的条目求和给定ID的金额值。
SELECT M.ID, M.Start, M.Finish, SUM(D.Amount)
FROM Data AS D,
(SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS (SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS (SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS (SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish)
OR (T2.Start <= L.Finish AND L.Finish < T2.Finish)))) AS M
WHERE D.ID = M.ID
AND M.Start <= D.Start
AND M.Finish >= D.Finish
GROUP BY M.ID, M.Start, M.Finish
ORDER BY M.ID, M.Start;
This gives:
ID Start Finish Amount
01 2008-10-01 2008-10-02 10
01 2008-10-03 2008-10-05 61
02 2008-10-02 2008-10-03 20
02 2008-10-06 2008-10-08 11
03 2008-10-05 2008-10-06 14
03 2008-10-05 2008-11-08 33 -- Here be trouble!
03 2008-10-08 2008-11-08 19
Edited: This is almost the correct data set on which to do the COUNT and SUM aggregation requested by the original question, so the final answer is:
编辑:这几乎是正确的数据集,用于执行原始问题所请求的COUNT和SUM聚合,因此最终答案是:
SELECT I.ID, COUNT(*) AS Number, SUM(I.Amount) AS Amount
FROM (SELECT M.ID, M.Start, M.Finish, SUM(D.Amount) AS Amount
FROM Data AS D,
(SELECT DISTINCT F.ID, F.Start, L.Finish
FROM Data AS F, Data AS L
WHERE F.Start < L.Finish
AND F.ID = L.ID
-- There are no gaps between F.Finish and L.Start
AND NOT EXISTS
(SELECT *
FROM Data AS M
WHERE M.ID = F.ID
AND F.Finish < M.Start
AND M.Start < L.Start
AND NOT EXISTS
(SELECT *
FROM Data AS T1
WHERE T1.ID = F.ID
AND T1.Start < M.Start
AND M.Start <= T1.Finish))
-- Cannot be extended further
AND NOT EXISTS
(SELECT *
FROM Data AS T2
WHERE T2.ID = F.ID
AND ((T2.Start < F.Start AND F.Start <= T2.Finish) OR
(T2.Start <= L.Finish AND L.Finish < T2.Finish)))
) AS M
WHERE D.ID = M.ID
AND M.Start <= D.Start
AND M.Finish >= D.Finish
GROUP BY M.ID, M.Start, M.Finish
) AS I
GROUP BY I.ID
ORDER BY I.ID;
id number amount
01 2 71
02 2 31
03 3 66
Review: Oh! Drat...the entry for 3 has twice the 'amount' that it should have. Previous 'edited' parts indicate where things started to go wrong. It looks as though either the first query is subtly wrong (maybe it is intended for a different question), or the optimizer I'm working with is misbehaving. Nevertheless, there should be an answer closely related to this that will give the correct values.
评论:哦! Drat ... 3的条目有两倍于它应该具有的'金额'。之前的“已编辑”部分表示事情开始出错的地方。看起来好像第一个查询是巧妙的错误(可能是针对不同的问题),或者我正在使用的优化器是行为不端。然而,应该有一个与此密切相关的答案,它将给出正确的值。
For the record: tested on IBM Informix Dynamic Server 11.50 on Solaris 10. However, should work fine on any other moderately standard-conformant SQL DBMS.
对于记录:在Solaris 10上的IBM Informix Dynamic Server 11.50上进行了测试。但是,应该可以在任何其他符合标准的标准SQL DBMS上正常工作。
#2
1
Probably need to create a cursor and loop through the results, keeping track of which id you are working with and accumulating the data along the way. When the id changes you can insert the accumulated data into a temporary table and return the table at the end of the procedure (select all from it). A table-based function might be better as you can then just insert into the return table as you go along.
可能需要创建一个游标并循环遍历结果,跟踪您正在使用的ID以及沿途累积数据。当id更改时,您可以将累积的数据插入临时表,并在过程结束时返回表(从中选择全部)。基于表的函数可能会更好,因为您可以随时插入返回表。
#3
1
I suspect that it may require iteration of some sort but I don't want to go down that path.
我怀疑它可能需要某种迭代,但我不想走那条路。
I think that's the route you'll have to take, use a cursor to populate a table variable. If you have a large number of records you could use a permanent table to store the results then when you need to retrieve the data you could process only the new data.
我认为这是你必须采取的路线,使用光标填充表变量。如果您有大量记录,则可以使用永久表来存储结果,然后当您需要检索数据时,您只能处理新数据。
I would add a bit field with a default of 0 to the source table to keep track of which records have been processed. Assuming no one is using select * on the table, adding a column with a default value won't affect the rest of your application.
我将向源表添加一个默认值为0的位字段,以跟踪已处理的记录。假设没有人在表上使用select *,添加具有默认值的列将不会影响应用程序的其余部分。
Add a comment to this post if you want help coding the solution.
如果您需要帮助编写解决方案,请在此帖子中添加评论。
#4
0
Well I decided to go down the iteration route using a mixture of joins and cursors. By JOINing the data table against itself I can create a link list of only those records that are consecutive.
好吧,我决定使用连接和游标的混合来沿着迭代路线前进。通过将数据表连接到自身,我可以创建仅包含连续记录的链接列表。
INSERT INTO #CONSEC
SELECT a.ID, a.Start, b.Finish, b.Amount
FROM Data a JOIN Data b
ON (a.Finish = b.Start) AND (a.ID = b.ID)
Then I can unwind the list by iterating over it with a cursor, and doing updates back to the data table to adjust (And delete the now extraneous records from the Data table)
然后我可以通过使用游标迭代它来展开列表,并将更新返回到数据表进行调整(并从Data表中删除现在无关的记录)
DECLARE CCursor CURSOR FOR
SELECT ID, Start, Finish, Amount FROM #CONSEC ORDER BY Start DESC
@Total = 0
OPEN CCursor
FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
WHILE @FETCH_STATUS = 0
BEGIN
@Total = @Total + @Amount
@Start_Last = @Start
@Finish_Last = @Finish
@ID_Last = @ID
DELETE FROM Data WHERE Start = @Finish
FETCH NEXT FROM CCursor INTO @ID, @START, @FINISH, @AMOUNT
IF (@ID_Last<> @ID) OR (@Finish<>@Start_Last)
BEGIN
UPDATE Data
SET Amount = Amount + @Total
WHERE Start = @Start_Last
@Total = 0
END
END
CLOSE CCursor
DEALLOCATE CCursor
This all works and has acceptable performance for typical data that I am using.
这一切都有效,并且对于我正在使用的典型数据具有可接受的性能。
I did find one small issue with the above code. Originally I was updating the Data table on each loop through the cursor. But this didn't work. It seems that you can only do one update on a record, and that multiple updates (in order to keep adding data) revert back to the reading the original contents of the record.
我确实在上面的代码中发现了一个小问题。最初我是通过游标更新每个循环上的Data表。但这没效果。您似乎只能对记录执行一次更新,并且多次更新(为了保持添加数据)还原为读取记录的原始内容。