表有成对匹配的记录，只需选择和更新一条记录

发布于 2024-11-07 20:30:49 字数 1896 浏览 0 评论 0原文

我有一个包含成对匹配记录的表，我像这样查询：

select id,name,amount,type from accounting_entries 
  where name like "%05" and amount != 0 order by name limit 10;

结果：

+------+----------------------+--------+-------+
| id   | name                 | amount | type  |
+------+----------------------+--------+-------+
|  786 | D-1194-838HELLUJP-05 |  -5800 | DEBIT | 
|  785 | D-1194-838HELLUJP-05 |  -5800 | DEBIT | 
| 5060 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT | 
| 5059 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT | 
|  246 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT | 
|  245 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT | 
| 9720 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT | 
| 9719 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT | 
| 2694 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT | 
| 2693 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT | 
+------+----------------------+--------+-------+
10 rows in set (0.01 sec)

我需要执行更新，以便结果数据如下所示：

+------+----------------------+--------+--------+
| id   | name                 | amount | type   |
+------+----------------------+--------+--------+
|  786 | D-1194-838HELLUJP-05 |  -5800 | DEBIT  | 
|  785 | C-1194-838HELLUJP-05 |   5800 | CREDIT | 
| 5060 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT  | 
| 5059 | C-1195-UOK4HS5POF-05 |   5000 | CREDIT | 
|  246 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT  | 
|  245 | C-1196-0FUCJI66BX-05 |   7000 | CREDIT | 
| 9720 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT  | 
| 9719 | C-1197-W2J0EC1BOB-05 |   6500 | CREDIT | 
| 2694 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT  | 
| 2693 | C-1198-MFKIKHGW0S-05 |   5500 | CREDIT | 
+------+----------------------+--------+--------+
10 rows in set (0.01 sec)

一个条目应该否定另一个条目。我更新第一条还是第二条匹配记录并不重要，重要的是一个有正数，另一个有负数。并且类型和名称需要更新。

有关如何执行此操作的任何线索？更新命令是什么样的？也许使用group by子句？我对如何使用存储过程执行此操作有一些想法，但是我可以通过简单的更新来执行此操作吗？

原文

I have a table with pairs of matching records that I query like this:

select id,name,amount,type from accounting_entries 
  where name like "%05" and amount != 0 order by name limit 10;

Results:

+------+----------------------+--------+-------+
| id   | name                 | amount | type  |
+------+----------------------+--------+-------+
|  786 | D-1194-838HELLUJP-05 |  -5800 | DEBIT | 
|  785 | D-1194-838HELLUJP-05 |  -5800 | DEBIT | 
| 5060 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT | 
| 5059 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT | 
|  246 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT | 
|  245 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT | 
| 9720 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT | 
| 9719 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT | 
| 2694 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT | 
| 2693 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT | 
+------+----------------------+--------+-------+
10 rows in set (0.01 sec)

I need to perform an update so that the resulting data will look like this:

+------+----------------------+--------+--------+
| id   | name                 | amount | type   |
+------+----------------------+--------+--------+
|  786 | D-1194-838HELLUJP-05 |  -5800 | DEBIT  | 
|  785 | C-1194-838HELLUJP-05 |   5800 | CREDIT | 
| 5060 | D-1195-UOK4HS5POF-05 |  -5000 | DEBIT  | 
| 5059 | C-1195-UOK4HS5POF-05 |   5000 | CREDIT | 
|  246 | D-1196-0FUCJI66BX-05 |  -7000 | DEBIT  | 
|  245 | C-1196-0FUCJI66BX-05 |   7000 | CREDIT | 
| 9720 | D-1197-W2J0EC1BOB-05 |  -6500 | DEBIT  | 
| 9719 | C-1197-W2J0EC1BOB-05 |   6500 | CREDIT | 
| 2694 | D-1198-MFKIKHGW0S-05 |  -5500 | DEBIT  | 
| 2693 | C-1198-MFKIKHGW0S-05 |   5500 | CREDIT | 
+------+----------------------+--------+--------+
10 rows in set (0.01 sec)

One entry should negate the other entry. It doesn't matter if I update the first or second matching record, what matters is that one has a positive amount and the other has a negative amount. And the type and name need to be updated.

Any clues on how to do this? What would the update command look like? Maybe using a group by clause? I have some ideas on how to do it with a stored procedure, but can I do it with a simple update?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月隐月明月朦胧 2024-11-14 20:30:49

试试这个：

UPDATE accounting_entries as ae
SET    name = 'C' + SubString(name, 1, Length(name) - 1))
       amount = amount * -1
       type = 'Credit'    
WHERE  id = 
         (SELECT MIN(id) FROM 
            (SELECT * FROM accounting_entries) as temp
          GROUP BY name)

关键是 WHERE 部分中的子查询，它将更新限制为每个名称值的最低 ID。假设较低的 ID 是您始终想要更新的 ID。如果这不正确，则根据您将使用的任何规则更新子查询。

编辑：根据发现的技术更新子查询这里，由于mysql定义的限制此处。

这个查询提供了一种立即更新所有记录的方法（因为这似乎就是OP正在寻找的东西。但是，最有效的方法是枚举代码中的所有记录（php、asp.net、等），并通过基于代码的方法更新需要更改的行，这将消除在 mysql 中运行子查询更新所固有的性能问题。

Try this:

UPDATE accounting_entries as ae
SET    name = 'C' + SubString(name, 1, Length(name) - 1))
       amount = amount * -1
       type = 'Credit'    
WHERE  id = 
         (SELECT MIN(id) FROM 
            (SELECT * FROM accounting_entries) as temp
          GROUP BY name)

The key is the subquery in the WHERE section that limits the updates to the lowest ID of each name value. The assumption is that the lower ID is the one that you will always want to update. If this is not correct, then update the subquery based on whatever rule you would use.

Edit: Update to subquery based on technique found here, due to limitation on mysql defined here.

This query gives a method for updating all records at once (as it seemed like this is what the OP was looking for. However, the most efficient way to do this would be to enumerate through all records in code (php, asp.net, etc), and through code-based methods update the rows that needed to change. This would eliminate the performance issues inherent with running updates off of subqueries in mysql.

回复收藏 0 原文

欢烬 2024-11-14 20:30:49

如果一对的 ID:s 始终与公式 x 和 x+1 匹配，您可以这样说

WHERE MOD(`id`, 2) = 1

编辑：我还没有测试过这段代码，所以我不能保证可以将列名放入 MOD 中像这样，但可能值得一试和/或进一步调查。

If the ID:s for a pair always match the formula x and x+1, you could say something like

WHERE MOD(`id`, 2) = 1

EDIT: I haven't tested this code, so I can't guarantee that it's possible to put a column name into a MOD like this, but it might be worth a try, and/or further investigation.

回复收藏 0 原文