首先,向表中添加一个唯一的列,然后可以使用找到的相同sql语法。
例如,添加
ID
列(主键,自动递增)
+----+----------+------+
| ID | Time | AR |
+----+----------+------+
| 1 | 00:00:00 | 0.12 |
| 2 | 01:00:00 | 0.16 |
| 3 | 02:00:00 | 0.13 |
| 4 | 03:00:00 | 0.19 |
| 5 | 04:00:00 | 0.11 |
| 6 | 00:00:00 | 0.15 |
| 7 | 01:00:00 | 0.34 |
| 8 | 02:00:00 | 0.56 |
| 9 | 03:00:00 | 0.67 |
| 10 | 04:00:00 | 0.92 |
+----+----------+------+
SQL:
set @arsum_1 := 0;
set @arsum_2 := 0;
set @arsum_3 := 0;
select t.Time, t.AR, ROUND(IFNULL(t1.A1, 0),2) as `1`, ROUND(IFNULL(t2.A2, 0), 2) as `2`, ROUND(IFNULL(t3.A3, 0),2) as `3` from time_sample t
natural left join (select ID, @arsum_1 := @arsum_1 + AR as A1 from time_sample where Time='01:00') as t1
natural left join (select ID, @arsum_2 := @arsum_2 + AR as A2 from time_sample where Time='02:00') as t2
natural left join (select ID, @arsum_3 := @arsum_3 + AR as A3 from time_sample where Time='03:00') as t3 order by t.Time;
输出:
+----------+------+------+------+------+
| Time | AR | 1 | 2 | 3 |
+----------+------+------+------+------+
| 00:00:00 | 0.12 | 0.00 | 0.00 | 0.00 |
| 00:00:00 | 0.15 | 0.00 | 0.00 | 0.00 |
| 01:00:00 | 0.16 | 0.16 | 0.00 | 0.00 |
| 01:00:00 | 0.34 | 0.50 | 0.00 | 0.00 |
| 02:00:00 | 0.13 | 0.00 | 0.13 | 0.00 |
| 02:00:00 | 0.56 | 0.00 | 0.69 | 0.00 |
| 03:00:00 | 0.19 | 0.00 | 0.00 | 0.19 |
| 03:00:00 | 0.67 | 0.00 | 0.00 | 0.86 |
| 04:00:00 | 0.11 | 0.00 | 0.00 | 0.00 |
| 04:00:00 | 0.92 | 0.00 | 0.00 | 0.00 |
+----------+------+------+------+------+
