主页 / dba / 问题 / 94545
Accepted
Mohamed Anis Dahmani
Asked: 2015-03-06 16:43:52 +0800 CST

根据先前的和实际的行值计算行值

  • 772

大家好，感谢您的帮助。
我有以下情况：一个名为 statements 的表，其中包含字段id (int)、stmnt_date (date)、debit (double)、credit (double) 和balance (double) 我的桌子的结构

我想按照以下规则计算余额：

第一行余额（按时间顺序）=借方-贷方，其余行

当前行余额 = 按时间顺序排列的前一行余额 + 当前行借方 - 当前行贷方

正如您在上面的图片中看到的那样，行没有按日期排列，这就是为什么我按时间顺序两次使用这个词来强调 stmnt_date 值的重要性。

非常感谢您的帮助。

mysql row

5 个回答

  1. Best Answer

    假设stmnt_date有一个UNIQUE约束，使用窗口/分析函数会很容易：

    SELECT 
    s.stmnt_date, s.debit, s.credit,
    SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
        AS balance
FROM
    statements AS s
ORDER BY
    stmnt_date ;

    不幸的是，MySQL（还）没有实现分析功能。您可以使用严格的 SQL，通过自连接表（虽然 100% 工作但效率应该相当低）或通过使用特定的 MySQL 功能、变量（这将非常有效，但您必须对其进行测试）来解决问题升级 mysql 时，确保结果仍然正确，并且不会因某些优化改进而受损）：

    SELECT 
    s.stmnt_date, s.debit, s.credit,
    @b := @b + s.debit - s.credit AS balance
FROM
    (SELECT @b := 0.0) AS dummy 
  CROSS JOIN
    statements AS s
ORDER BY
    stmnt_date ;

    使用您的数据，它将导致：

    +------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014-05-15 |  3000 |      0 |    3000 |
| 2014-06-17 | 20000 |      0 |   23000 |
| 2014-07-16 |     0 |   3000 |   20000 |
| 2014-08-14 |     0 |   3000 |   17000 |
| 2015-02-01 |  3000 |      0 |   20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)
    • 13

  2. 我认为您可以尝试以下方法：

    set @balance := 0;

SELECT stmnt_date, debit, credit, (@balance := @balance + (debit - credit)) as Balance
FROM statements
ORDER BY stmnt_date;
    • 6

  3. ypercube 的答案非常壮观（我从未见过通过这样的虚拟选择在单个查询中创建变量），所以为了您的方便，这里是 CREATE TABLE 语句。

    对于 Google Image Search 中的表格数据图像，您可以使用https://convertio.co/ocr/或https://ocr.space/将其转换为文本文档。然后，如果 OCR 没有正确检测列，并且您有 Mac，请使用TextWrangler并按住选项键来执行矩形选择并四处移动列。Sequel Pro、TextWrangler 等SQL 编辑器和Google Docs等电子表格的组合使得处理制表符分隔的表格数据非常有效。

    如果我可以将所有这些都放在评论中，我会的，所以请不要赞成这个答案。

    -- DROP TABLE statements;

CREATE TABLE IF NOT EXISTS statements (
  id integer NOT NULL AUTO_INCREMENT,
  stmnt_date date,
  debit integer not null default 0,
  credit integer not null default 0,
  PRIMARY KEY (id)
);

INSERT INTO statements
(stmnt_date  , debit, credit) VALUES
('2014-06-17', 20000, 0     ),
('2014-08-14', 0    , 3000  ),
('2014-07-16', 0    , 3000  ),
('2015-02-01', 3000 , 0     ),
('2014-05-15', 3000 , 0     );

-- this is slightly modified from ypercube's (@b := 0 vs @b := 0.0)
SELECT 
    s.stmnt_date, s.debit, s.credit,
    @b := @b + s.debit - s.credit AS balance
FROM
    (SELECT @b := 0) AS dummy 
CROSS JOIN
    statements AS s
ORDER BY
    stmnt_date ASC;

/* result
+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014-05-15 |  3000 |      0 |    3000 |
| 2014-06-17 | 20000 |      0 |   23000 |
| 2014-07-16 |     0 |   3000 |   20000 |
| 2014-08-14 |     0 |   3000 |   17000 |
| 2015-02-01 |  3000 |      0 |   20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)
*/
    • 2

  4. 自连接表在大表上不是很快。所以在 PostgreSQL 上处理这个任务我决定使用触发函数来计算存储字段“余额”。每行的所有计算只发生一次。

    DROP TABLE IF EXISTS statements;

CREATE TABLE IF NOT EXISTS statements (
  id BIGSERIAL,
  stmnt_date TIMESTAMP,
  debit NUMERIC(18,2) not null default 0,
  credit NUMERIC(18,2) not null default 0,
  balance NUMERIC(18,2)
);

CREATE OR REPLACE FUNCTION public.tr_fn_statements_balance()
RETURNS trigger AS
$BODY$
BEGIN

    UPDATE statements SET
    balance=(SELECT SUM(a.debit)-SUM(a.credit) FROM statements a WHERE a.stmnt_date<=statements.stmnt_date)
    WHERE stmnt_date>=NEW.stmnt_date;

RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER tr_statements_after_update
  AFTER INSERT OR UPDATE OF debit, credit
  ON public.statements
  FOR EACH ROW
  EXECUTE PROCEDURE public.tr_fn_statements_balance();


INSERT INTO statements
(stmnt_date  , debit, credit) VALUES
('2014-06-17', 20000, 0     ),
('2014-08-14', 0    , 3000  ),
('2014-07-16', 0    , 3000  ),
('2015-02-01', 3000 , 0     ),
('2014-05-15', 3000 , 0     );


select * from statements order by stmnt_date;
    • 1

  5. 例如，在 MSSQL 中：

    使用 with() 语句生成 CTE。这本质上是一个临时结果集，它将显示每一行的值。您可以在 with 语句中使用 math 在末尾创建一列，使用 math 显示该行的总计为 DEBIT-CREDIT。在您的 with 语句中，您需要为每一行分配行号，使用 WITH () 的 OVER 子句按 stmnt_date 排序。

    然后，使用 a.ROWNUMBER= b.ROWNUMBER-1 或 +1 递归地将表连接到自身上，这将允许您引用此行和上一行的 a.total+b.total= 总计。

    我很感激我没有提供代码，但这是实现这一目标的实用方法。如果需要，我可以提供代码:)

    • -1

相关问题