计算mysql中两行金额之差

我有一张可以计算的table balance ，表中有以下列：

> describe tbl_credit_log

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| visitor_id       | int(10) unsigned | YES  | MUL | NULL    |                |
| post_owner_id    | int(10) unsigned | YES  | MUL | NULL    |                |
| post_id          | int(10) unsigned | YES  | MUL | NULL    |                |
| balance          | double(50,6)     | YES  |     | NULL    |                |
| credits          | double(50,6)     | YES  |     | NULL    |                |
| debits           | double(50,6)     | YES  |     | NULL    |                |
| trans_id         | varchar(255)     | NO   |     | NULL    |                |
| trans_amount     | double(50,6)     | YES  |     | NULL    |                |
| earningtype      | varchar(15)      | NO   |     | NULL    |                |
| created_at       | timestamp        | YES  |     | NULL    |                |
| updated_at       | timestamp        | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

这里是我要计算的几个条目：

+---------+------------+---------------+---------+------------------+-------------+-----------+--------+---------------------+
| id      | visitor_id | post_owner_id | post_id | advertisement_id | balance     | credits   | debits | created_at          |
+---------+------------+---------------+---------+------------------+-------------+-----------+--------+---------------------+
| 4331666 |      11006 |            11 |    NULL |             NULL | 3639.624400 | 22.500000 |   NULL | 2018-08-10 05:45:37 |
| 4364034 |       7206 |            11 |    NULL |             NULL | 5139.607900 | 22.500000 |   NULL | 2018-08-10 11:02:52 |
| 4377238 |       4353 |            11 |    NULL |             NULL | 5162.107900 | 22.500000 |   NULL | 2018-08-10 12:52:01 |
| 4485288 |       9664 |            11 |    NULL |             NULL | 5184.607900 | 22.500000 |   NULL | 2018-08-11 08:58:19 |
| 4544185 |      11709 |            11 |    NULL |             NULL | 5207.107900 | 22.500000 |   NULL | 2018-08-11 19:06:52 |
| 4550728 |      11970 |            11 |    NULL |             NULL | 5229.607900 | 22.500000 |   NULL | 2018-08-11 20:39:36 |
| 4607317 |      12021 |            11 |    NULL |             NULL | 5252.107900 | 22.500000 |   NULL | 2018-08-12 07:29:17 |
| 4629660 |      11926 |            11 |    NULL |             NULL | 5274.607900 | 22.500000 |   NULL | 2018-08-12 09:18:56 |
| 4725299 |      12088 |            11 |    NULL |             NULL | 5297.107900 | 22.500000 |   NULL | 2018-08-13 01:54:53 |
| 4725347 |      10253 |            11 |    NULL |             NULL | 5319.607900 | 22.500000 |   NULL | 2018-08-13 08:29:46 |
| 4725357 |      12140 |            11 |    NULL |             NULL | 5342.107900 | 22.500000 |   NULL | 2018-08-13 09:27:44 |
+---------+------------+---------------+---------+------------------+-------------+-----------+--------+---------------------+

在这里 we have difference (more then 50) in amount on id number 4364034，我想在整个数据库中找到，其中用户id post_owner_id 作为外键。
我想得到所有的 post_owner_id 他们在数量上有差异 created_at .
所以预期的结果是这样的

+---------+------------+---------------+---------+------------------+-------------+-----------+--------+---------------------+
    | id      | visitor_id | post_owner_id | post_id | advertisement_id | balance     | credits   | debits | created_at          |
    +---------+------------+---------------+---------+------------------+-------------+-----------+--------+---------------------+
    | 4364034 |       7206 |            11 |    NULL |             NULL | 5139.607900 | 22.500000 |   NULL | 2018-08-10 11:02:52 |
    +---------+------------+---------------+---------+------------------+-------------+-----------+--------+---------------------+

这里我们将有多个 post_owner_id . 我正在展示一个 post_owner_id 因为这个结果只有一个post-owner-id，但是我想得到不同的post-owner-id balance 超过50岁。
我不知道该怎么做。我想比较一下价格 group_by 过帐\u所有者\u id并获取差额金额所在的行。

考虑以下几点：

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,balance DECIMAL(12,6) NOT NULL
,created_at timestamp NOT NULL 
);

INSERT INTO my_table VALUES
(4331666,3639.624400,'2018-08-10 05:45:37'),
(4364034,5139.607900,'2018-08-10 11:02:52'),
(4377238,5162.107900,'2018-08-10 12:52:01'),
(4485288,5184.607900,'2018-08-11 08:58:19'),
(4544185,5207.107900,'2018-08-11 19:06:52'),
(4550728,5229.607900,'2018-08-11 20:39:36'),
(4607317,5252.107900,'2018-08-12 07:29:17'),
(4629660,5274.607900,'2018-08-12 09:18:56'),
(4725299,5297.107900,'2018-08-13 01:54:53'),
(4725347,5319.607900,'2018-08-13 08:29:46'),
(4725357,5342.107900,'2018-08-13 09:27:44');

方案1：

SELECT a.* 
  FROM 
     ( SELECT x.*
            , MIN(y.id) y_id 
         FROM my_table x 
         JOIN my_table y 
           ON y.created_at > x.created_at 
        GROUP 
           BY x.id
     ) a 
  JOIN my_table b 
    ON b.id = a.y_id 
   AND b.balance > a.balance + 50;
+---------+-------------+---------------------+---------+
| id      | balance     | created_at          | y_id    |
+---------+-------------+---------------------+---------+
| 4331666 | 3639.624400 | 2018-08-10 05:45:37 | 4364034 |
+---------+-------------+---------------------+---------+

方案2：

SELECT id
     , balance
     , created_at
  FROM 
     ( SELECT x.*
            , @i<balance-50 i
            , @i:=balance
         FROM my_table x
            , (SELECT @i:=null) vars
        ORDER
           BY created_at
     ) n
 WHERE i = 1;

 +---------+-------------+---------------------+
 | id      | balance     | created_at          |
 +---------+-------------+---------------------+
 | 4364034 | 5139.607900 | 2018-08-10 11:02:52 |
 +---------+-------------+---------------------+

您可以调整上述任一技术以返回任一行

计算mysql中两行金额之差

1条答案

相关问题

热门标签

最新问答