mysql 如何从语句中每个条目的前几行字段值中减去插入值

46scxncf 于 2023-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(85)

我需要对当天的每批货做一个插入。从前一行中扣除总金额时遇到问题。我插入到tableC，但因为如果我有多个订单在一天内，它只是抓住最后一个借方条目，并从原来的最后一行总扣除。我知道这是因为我使用了ORDER BY DESC LIMIT 1，但我想不出其他方法来获取最后一行的列值。我还需要第二次插入来获得前一次插入的新总数。
我在一个变量中使用了一个select语句来在我的insert语句中运行。@vTotal
如果它是一个单一的条目插入，它会工作，但我如何工作，如果有多个行在选择语句，或者有一个更好的方法来做到这一点？
下面是我目前的测试查询：

SET @vCreatedbt := 'name@domain.com';
SET @vTotal := (SELECT tableC.total - tableB.ShipmentCharge AS total 
                FROM tableC, tableB 
                ORDER BY id DESC 
                LIMIT 1);
SET @vwork_order_id := null;
SET @vModified := null;
SET @vModified_by := null;
SET @vCredit := null;
SET @vid := null;

INSERT INTO tableC
    SELECT @vId AS id, client_name AS descr, @vWork_order_id AS work_order_id, 
            @vCredit AS credit, ShipmentCharge as debit, @vTotal AS total, 
            now() as created, @vCreatedby as createdby, 
            @vModified AS modified, @vModified_by AS modified_by
    FROM tblA
        INNER JOIN tableD ON tableA.id = tableD.id
        INNER JOIN tableB ON tableD.tracking_number = tableB.TrackingNumber
        WHERE tableA.status = 'Order Shipped'

为了简化，只显示tableC的这三个字段
| id|借方|总计|
| - -----|- -----|- -----|
| 1|二十七点三七|15260.96|
查询是给我
| id|借方|总计|
| - -----|- -----|- -----|
| 1|二十七点三七|15260.96|
| 2|二十五点九六|15235.96|
| 3|二十五点|15235.96|
id自动递增

mysql

来源：https://stackoverflow.com/questions/76434255/how-to-subtract-inserted-value-from-previous-rows-field-value-for-each-entry-in

1条答案

按热度按时间

qyyhg6bp1#

DEMO：

CREATE TABLE balance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  person_id INT NOT NULL,
  action DECIMAL(10, 2) NOT NULL,
  balance DECIMAL(10, 2) NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  CONSTRAINT balance_cannot_be_negative CHECK (balance >= 0)
);

CREATE TRIGGER set_balance
BEFORE INSERT ON balance
FOR EACH ROW
  SET NEW.balance = COALESCE(
    (
    SELECT balance 
    FROM balance
    WHERE person_id = NEW.person_id
    ORDER BY created_at DESC LIMIT 1
    ), 0) + NEW.action;

INSERT INTO balance (person_id, action) VALUES (1, 100); -- add $100, balance $100
INSERT INTO balance (person_id, action) VALUES (1, -20); -- spend $20, balance $80
INSERT INTO balance (person_id, action) VALUES (2, 50);  -- add $50, balance $50
INSERT INTO balance (person_id, action) VALUES (1, 10);  -- add $10, balance $90
INSERT INTO balance (person_id, action) VALUES (2, 30);  -- add $30, balance $80
INSERT INTO balance (person_id, action) VALUES (2, -40); -- spend $40, balance $40

-- try to spend $100 which is over current balance, error generated
INSERT INTO balance (person_id, action) VALUES (2, -100);

Check constraint 'balance_cannot_be_negative' is violated.

SELECT * FROM balance ORDER BY id;
SELECT * FROM balance ORDER BY person_id, created_at;

| id|个人ID|作用|天平|创建于|
| - -----|- -----|- -----|- -----|- -----|
| 1| 1|一百|一百|2023 - 06 - 08 18：05：59.811375|
| 2| 1|-20.00| 80| 2023 - 06 - 08 18：05：59.817546|
| 3| 2| 50块|50块|2023 - 06 - 08 18：05：59.819130|
| 4| 1|十点|90| 2023 - 06 - 08 18：05：59.820016|
| 5个|2|三十|80| 2023 - 06 - 08 18：05：59.821140|
| 六|2|-40.00|四十|2023 - 06 - 08 18：05：59.822273|
| id|个人ID|作用|天平|创建于|
| - -----|- -----|- -----|- -----|- -----|
| 1| 1|一百|一百|2023 - 06 - 08 18：05：59.811375|
| 2| 1|-20.00| 80| 2023 - 06 - 08 18：05：59.817546|
| 4| 1|十点|90| 2023 - 06 - 08 18：05：59.820016|
| 3| 2| 50块|50块|2023 - 06 - 08 18：05：59.819130|
| 5个|2|三十|80| 2023 - 06 - 08 18：05：59.821140|
| 六|2|-40.00|四十|2023 - 06 - 08 18：05：59.822273|
fiddle
小提琴中需要的DATETIME(6)-普通DATETIME不提供所需的精度。在实践中，DATETIME必须足够-我怀疑这个人可以在一秒钟内执行两个单独的交易。也可以不使用created_at，而使用id来进行行排序。

赞(0）回复(0）举报 2023-06-21

我来回答

mysql 如何从语句中每个条目的前几行字段值中减去插入值

1条答案

DEMO：

相关问题

热门标签

最新问答