SQL如何从第2行，第2行，第3行的结果中减去第1行的结果 [英] SQL how to subtract result row 1 from row 2, row 2 from row 3

问题描述

在MySQL中，如何从第2行减去第1行，从第3行减去第2行，以此类推?我从中提取数据的表包含多个产品，并且所有产品都有多个价格(在不同的日期)

How do I subtract row 1 from row 2 and row 2 from row 3, etc. in MySQL? The table i am pulling my data from contains multiple products and all products have multiple prices (on different dates)

我正在使用的代码:

 SELECT 
        orderline_sales.product_name,
        orderline_sales.price
    FROM
        orderline_sales         
    GROUP BY price
    HAVING orderline_sales.product_name = 'Ibuprofen';

我得到的结果:

|---------------------|------------------|
|      product_name   |     price        |
|---------------------|------------------|
|       Ibuprofen     |      30.20       |
|---------------------|------------------|
|       Ibuprofen     |      32.20       |
|---------------------|------------------|
|       Ibuprofen     |      35.20       |
|---------------------|------------------|

我想要的结果:

|---------------------|------------------|------------------|
|      product_name   |     price        |   price_change   |
|---------------------|------------------|------------------|
|       Ibuprofen     |      30.20       |         0        |
|---------------------|------------------|------------------|
|       Ibuprofen     |      32.20       |         2        |
|---------------------|------------------|------------------|
|       Ibuprofen     |      35.20       |         3        |
|---------------------|------------------|------------------|

推荐答案

您可能想研究MySQL的

You probably want to look into MySQL's user defined variables, and then you probably want to do something like this:

SET @prev := NULL;
SELECT
    DATE(created_at),
    price - COALESCE(@prev, price) AS price_change,
    name,
    (@prev := price) AS price FROM (
        SELECT * FROM items ORDER BY DATE(created_at)
    ) t1
GROUP BY
    name, price, DATE(created_at)
HAVING name = 'Ibuprofen'
ORDER BY DATE(created_at);
Query OK, 0 rows affected (0.00 sec)

我还没有检查语法，所以可能有点差，但这是一般性的想法.请注意，我添加了日期，以便您可以按日期排序，否则结果可能毫无意义.

I haven't checked syntax so it might be a little off but that is the general idea. Note that I added date so that you can order by it, otherwise the results may be meaningless.

只需在我的机器上运行它即可

Just ran this on my machine:

SET @prev := NULL;
SELECT
    DATE(created_at),
    price - COALESCE(@prev, price) AS price_change,
    name,
    (@prev := price) AS price FROM (
        SELECT * FROM items ORDER BY DATE(created_at)
    ) t1
GROUP BY
    name, price, DATE(created_at)
HAVING name = 'Ibuprofen'
ORDER BY DATE(created_at);

Query OK, 0 rows affected (0.00 sec)

+------------------+--------------+-----------+-------+
| DATE(created_at) | price_change | name      | price |
+------------------+--------------+-----------+-------+
| 2018-12-10       |            0 | Ibuprofen |   110 |
| 2018-12-13       |          -10 | Ibuprofen |   100 |
| 2018-12-13       |           20 | Ibuprofen |   120 |
+------------------+--------------+-----------+-------+

3 rows in set, 1 warning (0.00 sec)

SELECT * FROM items;
+----+-------+----------------+---------------------+
| id | price | name           | created_at          |
+----+-------+----------------+---------------------+
|  8 |   100 | Ibuprofen      | 2018-12-13 12:52:35 |
|  9 |   110 | Ibuprofen      | 2018-12-10 12:12:12 |
| 10 |   120 | Ibuprofen      | 2018-12-13 12:52:35 |
| 11 |  1000 | Something else | 2018-12-13 13:01:19 |
+----+-------+----------------+---------------------+

4 rows in set (0.00 sec)

这篇关于SQL如何从第2行，第2行，第3行的结果中减去第1行的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！