|
我有表 meter_readings 的列:id,date_taken,kwh。
}9 O, A6 b1 a我正在尝试将列中的两行相减kwh,然后将结果放入一个名为的别名中consumption。$ i8 ^) X5 _1 |4 M8 @( o$ O. d
我正在使用:
% a" w$ g: Q3 y( a; o) ]! kSELECT id, kwh COALESCE(kwh-(SELECT kwh FROM meter_readings WHERE id= id+1), kwh) AS consumption ; n* m! F9 B$ a
FROM meter_readings;8 R; }# {% x' F/ R$ V. p
我在消费别名中得到的内容与原始别名一样简单kwh:3 a* {8 _' K" n/ g
id date_taken kwh consumption
' v3 Z4 G) i- X- b0 i 1 2013-01-01 4567.89 4567.89 ) q' a: A8 x: I! g1 v6 m$ M6 F
2 2013-01-08 4596.71 4596.71
* e3 O; J5 C( @% E& V0 \& ] 3 2013-01-15 4607.89 4607.89/ K2 c' U" K6 d" K
我想要的是:
k7 F) x$ R& [' ?5 E2 R3 Q( g id date_taken kwh consumption 1 I# F/ s; H5 |6 b- X8 U
1 2013-01-01 4567.89 08 V1 |7 g: s: U2 Y- E" v8 C
2 2013-01-08 4596.71 28.11
8 V0 R; e$ \* ] 3 2013-01-15 4607.89 11.18! J. M- d) c! M; G% K7 z: L
所以id 1 = 0,因为这是第一个date_taken kwh读数,因此不需要消耗值。这试图计算一年中每周的千瓦时消耗量。: W# M0 _0 |& b6 C5 r, Z! a2 G
! o. T: k3 l. P8 s: @/ h解决方案:
" q6 M$ k* M& J+ G5 C" G, ^ , f) c7 M; \1 i4 ~2 f* K7 f
& a0 R2 k& C4 w/ \7 x# H9 Z: @/ t9 q; l% D( u
只需为表名指定一个别名,然后在相关子查询中为表指定一个不同的别名即可。像这样的东西:
2 c* ?1 }! y) n' T9 RSELECT
. f! J+ g3 d5 w m1.id,
+ o* a' w9 }* x. R m1.kwh,
2 v7 [& _+ `$ ~- e9 ~ COALESCE(m1.kwh - (SELECT m2.kwh
2 M9 s* s% B: D4 N) {7 \# a FROM meter_readings AS m2
! b+ g6 g7 h% g2 J* Z8 y, W6 Q+ k WHERE m2.id = m1.id + 1),% U: p& k0 q6 E! s0 j# L
m1.kwh) AS consumption ; q( E% u; z w1 i
FROM meter_readings AS m1;
" m$ R" o, ?6 qSQL小提琴演示
9 J3 z2 r; A* e" `; z J: Q这将为您提供:- Q7 w F" h! Z7 f3 |
| ID | KWH | CONSUMPTION |% a1 F7 N) K9 `& ]# U% l
------------------------------
$ f$ b1 Y3 N! j% F0 _, B| 1 | 4567.89 | 1141.18 |) Y/ E' [( X H# X' z5 E7 P
| 2 | 3426.71 | 1181.37 |
& l8 f' a+ d+ }2 M5 h, V9 D| 3 | 2245.34 | 2245.34 |" c$ G9 `# t& s- V/ w& R& V4 T
4 S9 A) v+ \/ z8 R+ V0 z更新1
9 O. B* h8 }: Z8 Q4 h, ~9 D8 l对于更新的样本数据,只需WHERE m2.id = m1.id - 1在相关子查询中使用with,COALESCE(...,& f% ~1 ~- G3 I0 d9 c) |# Y
0)这样第一个将为0。如下所示:
5 l; x% u: `6 P9 rSELECT
, i6 f1 n: i; F m1.id, / ]2 \9 { t" R! n
date_format(m1.date_taken, '%Y-%m-%d') AS date_taken," U s/ U1 J/ c4 i. G q, T4 l- b
m1.kwh,. W" h$ ^, U0 D! m9 L4 t
COALESCE(m1.kwh - (SELECT m2.kwh
4 [3 y- b7 C. _7 @ FROM meter_readings m2
% z2 x) N; W9 j0 t/ | WHERE m2.id = m1.id - 1), 0) AS consumption 7 L1 W O. O; t# a" w# l* I, }
FROM meter_readings m1;
+ B& V: _- T0 P) r0 l& m% }更新了SQL Fiddle演示8 `; h. L2 k; f4 L" G% i1 ?1 V
这将为您提供:$ h( w+ Q( O/ B4 A# m8 h1 F
| ID | DATE_TAKEN | KWH | CONSUMPTION |+ {* J0 _3 i! _7 f/ }& B2 M* b$ b. d: U
-------------------------------------------
' K# y, U' j* t' B% _& r| 1 | 2013-01-01 | 4567.89 | 0 |
' D6 D4 w: j7 m; J| 2 | 2013-01-08 | 4596.71 | 28.82 |
) [8 X9 e2 {7 N2 E3 c8 u0 x5 || 3 | 2013-01-15 | 4607.89 | 11.18 | |
|