mysql从列中减去两行并放入别名

I have table meter_readings with columns: id, date_taken, kwh.

我有表meter_readings列:id,date_taken,kwh。

I'm trying to subtract two rows in kwh column together and put the results into an alias called consumption.

我试图在kwh列中减去两行,并将结果放入一个名为consumption的别名中。

I'm using:

SELECT id, kwh COALESCE(kwh-(SELECT kwh FROM meter_readings WHERE id= id+1), kwh) AS consumption 
FROM meter_readings; 

What I get back in consumption alias is simple the same as the original kwh:

我在消费别名中得到的回报与原始的kwh相同:

 id   date_taken        kwh        consumption 
  1   2013-01-01      4567.89       4567.89 
  2   2013-01-08      4596.71       4596.71  
  3   2013-01-15      4607.89       4607.89

what I would like is:

我想要的是:

 id   date_taken        kwh        consumption 
  1   2013-01-01      4567.89          0
  2   2013-01-08      4596.71        28.11
  3   2013-01-15      4607.89        11.18

so id 1 = 0 because this is the first date_taken kwh reading so has no need for a consumption value. This is trying to calculate over a year the weekly kwh consumption.

所以id 1 = 0,因为这是第一个date_taken kwh读数,因此不需要消耗值。这是试图计算一周的每周kwh消耗量。

2 个解决方案

#1

Just give the table name an alias, and the table inside the correlated subquery a different alias name. Something like this:

只需为表名赋予别名,并在相关子查询中的表中使用不同的别名。像这样的东西:

SELECT 
  m1.id, 
  m1.kwh,
  COALESCE(m1.kwh - (SELECT m2.kwh 
                     FROM meter_readings AS m2
                     WHERE m2.id = m1.id + 1),
           m1.kwh) AS consumption 
FROM meter_readings AS m1; 

SQL Fiddle Demo

This will give you:

这会给你:

| ID |     KWH | CONSUMPTION |
------------------------------
|  1 | 4567.89 |     1141.18 |
|  2 | 3426.71 |     1181.37 |
|  3 | 2245.34 |     2245.34 |

---

Update 1

For the updated sample data, just use WHERE m2.id = m1.id - 1 inside the correlated subquery with COALESCE(..., 0) so that the first one will be 0. Like this:

对于更新的样本数据,只需在相关子查询中使用WHERE m2.id = m1.id - 1和COALESCE(...,0),这样第一个将为0.像这样:

SELECT 
  m1.id, 
  date_format(m1.date_taken, '%Y-%m-%d') AS date_taken,
  m1.kwh,
  COALESCE(m1.kwh - (SELECT m2.kwh 
                     FROM meter_readings m2
                     WHERE m2.id = m1.id - 1), 0) AS consumption 
FROM meter_readings m1; 

Updated SQL Fiddle Demo

This will give you:

这会给你:

| ID | DATE_TAKEN |     KWH | CONSUMPTION |
-------------------------------------------
|  1 | 2013-01-01 | 4567.89 |           0 |
|  2 | 2013-01-08 | 4596.71 |       28.82 |
|  3 | 2013-01-15 | 4607.89 |       11.18 |

#2

COALESCE returns the first non null value , so you have to try this query without COALESCE, and see whats the problem

COALESCE返回第一个非空值,因此您必须在没有COALESCE的情况下尝试此查询,并查看问题是什么