代码之家 › 专栏 › 技术社区 › smartrahat

显示适当的数量平衡

mysql

smartrahat · 技术社区 · 7 年前

我有一个问题,我想显示在哪里 debit , credit & balance 我没有 均衡 表中的列。我根据以下公式计算余额: 借记 & 信用 .

我试图创建一个余额列,在其中存储余额并从表中显示它。但如果我更新或删除任何行,余额将不正确。

我找到了下面的代码。但是,当一个日期有一行,而一个日期的多个列不能正常工作时,它可以工作。它逐日显示余额,我想逐行显示余额。

MySQL

SELECT 
    m.`id`,
    m.`date`, 
    m.`credit`, 
    m.`debit`,
    SUM(t.`credit`) - SUM(t.`debit`) AS `balance`
FROM `cash_book` m 
    JOIN (
        SELECT 
            `id`,
            `date`,
            `credit`,
            `debit`
        FROM 
            `cash_book`
    ) t ON t.`date` <= m.`date`
WHERE `customer_id` = 1
GROUP BY 
    m.`id`
ORDER BY m.`date` ASC

它返回的结果如下:

Date        Debit    Credit    Balance
2016-11-27      0      2000       2000
2016-12-02      0    500000     585000 //same result for date 2016-12-02
2016-12-02  15000         0     585000 //same result for date 2016-12-02
2016-12-02      0    100000     585000 //same result for date 2016-12-02
2016-12-03   1200         0     583800
2016-12-04   3160         0     580540 //same result for date 2016-12-04
2016-12-04    100         0     580540 //same result for date 2016-12-04
2016-12-05     30         0     580510
2016-12-06      0       150     580660

但我想要这样的结果:

Date        Debit    Credit    Balance
2016-11-27      0      2000       2000
2016-12-02      0    500000     502000 
2016-12-02  15000         0     487000 
2016-12-02      0    100000     587000 
2016-12-03   1200         0     585800
2016-12-04   3160         0     582640 
2016-12-04    100         0     582540 
2016-12-05     30         0     582510
2016-12-06      0       150     582660

4 回复 | 直到 7 年前

Bernd Buffen 7 年前

这就是你要找的吗?

SELECT 
    `id`,
    `date`,
    `credit`,
    `debit`,
    @balance := @balance + credit-debit AS balance
FROM `cash_book`
CROSS JOIN ( SELECT @balance := 0) as init
ORDER BY `date` ASC ;

样品

mysql> SELECT * FROM cash_book;
+------+------------+-------+--------+
| id   | date       | debit | credit |
+------+------------+-------+--------+
|    1 | 2016-11-27 |     0 |   2000 |
|    2 | 2016-12-04 |  3160 |      0 |
|    3 | 2016-12-02 | 15000 |      0 |
|    4 | 2016-12-03 |  1200 |      0 |
|    5 | 2016-12-05 |    30 |      0 |
|    6 | 2016-11-29 |     0 |  10000 |
|    7 | 2016-01-05 |     0 |      0 |
|    8 | 2016-12-01 |  2000 |      0 |
|    9 | 2016-11-29 | 10000 |      0 |
|   10 | 2016-12-02 |  2000 | 100000 |
|   11 | 2016-12-06 |  2000 |    150 |
|   12 | 2016-12-02 |  2000 | 500000 |
+------+------------+-------+--------+
12 rows in set (0,00 sec)

mysql> SELECT
    ->     `id`,
    ->     `date`,
    ->     `credit`,
    ->     `debit`,
    ->     @balance := @balance + credit-debit AS balance
    -> FROM `cash_book`
    -> CROSS JOIN ( SELECT @balance := 0) as init
    -> ORDER BY `date` ASC ;
+------+------------+--------+-------+---------+
| id   | date       | credit | debit | balance |
+------+------------+--------+-------+---------+
|    7 | 2016-01-05 |      0 |     0 |       0 |
|    1 | 2016-11-27 |   2000 |     0 |    2000 |
|    6 | 2016-11-29 |  10000 |     0 |   12000 |
|    9 | 2016-11-29 |      0 | 10000 |    2000 |
|    8 | 2016-12-01 |      0 |  2000 |       0 |
|    3 | 2016-12-02 |      0 | 15000 |  -15000 |
|   10 | 2016-12-02 | 100000 |  2000 |   83000 |
|   12 | 2016-12-02 | 500000 |  2000 |  581000 |
|    4 | 2016-12-03 |      0 |  1200 |  579800 |
|    2 | 2016-12-04 |      0 |  3160 |  576640 |
|    5 | 2016-12-05 |      0 |    30 |  576610 |
|   11 | 2016-12-06 |    150 |  2000 |  574760 |
+------+------------+--------+-------+---------+
12 rows in set (0,00 sec)

mysql>

Gurwinder Singh 7 年前

可以在中使用子查询 select 像这样

SELECT 
    m.`id`,
    m.`date`, 
    m.`credit`, 
    m.`debit`,
    (select sum(n.`credit`) - sum(n.`debit`)
      from `cash_book` n
      where n.`id` = m.`id`
      and n.`date` <= m.`date`) balance
FROM `cash_book` m
WHERE `customer_id` = 1
ORDER BY m.`date` ASC

denny 7 年前

尝试此查询

select s.Date,s.Debit,s.credit,ABS(@b := @b + s.debit - s.credit) as  balance from (select @b:= 0.0) as dummy cross join cash_book as s order by ID;

K.Suthagar 7 年前

在查询中,您使用了 JOIN ,这将被用来 join 两张或多张桌子。这里您只使用一张桌子。所以不需要 加入 在查询中。

您可以使用以下简单查询,

SELECT 
  m.id,
  m.date,
  m.credit,
  m.debit,
  (SELECT SUM(credit)-SUM(debit) 
     FROM`cash_book` A 
     WHERE A.date<=m.Date) 
FROM `cash_book` m WHERE m.customer_id = 1 ORDER BY m.Date ASC