MySQL从列中减去两行,并放入一个别名 [英] mysql subtract two rows from column and places into an alias
问题描述
我的表 meter_readings
的列为:id
,date_taken
,kwh
.
我正在尝试将kwh
列中的两行相减,并将结果放入一个名为consumption
的别名中.
I'm trying to subtract two rows in kwh
column together and put the results into an alias called consumption
.
我正在使用:
SELECT id, kwh COALESCE(kwh-(SELECT kwh FROM meter_readings WHERE id= id+1), kwh) AS consumption
FROM meter_readings;
我在消费别名中得到的内容与原始的kwh
一样简单:
What I get back in consumption alias is simple the same as the original 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
我想要的是:
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,因为这是第一个date_taken kwh读数,因此不需要消耗值.这试图计算一年中每周的千瓦时消耗量.
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.
推荐答案
只需为表名指定一个别名,并在相关子查询中为该表指定一个不同的别名.像这样:
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小提琴演示
这将为您提供:
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 |
更新1
对于更新的样本数据,只需在具有COALESCE(..., 0)
的相关子查询中使用WHERE m2.id = m1.id - 1
,这样第一个将为0.
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:
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;
更新了SQL Fiddle演示
这将为您提供:
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 |
这篇关于MySQL从列中减去两行,并放入一个别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!