mysql运行与group by的区别

Question

我正在试验的数据集具有此 SQLFiddle 中给出的结构.

create table readings_tab (id int, site varchar(15), logged_at datetime, reading smallint);

insert into readings_tab values (1, 'A', '2017-08-21 13:22:00', 2500);
insert into readings_tab values (2, 'B', '2017-08-21 13:22:00', 1210);
insert into readings_tab values (3, 'C', '2017-08-21 13:22:00', 3500);
insert into readings_tab values (4, 'A', '2017-08-22 13:22:00', 2630);
insert into readings_tab values (5, 'B', '2017-08-22 13:22:00', 1400);
insert into readings_tab values (6, 'C', '2017-08-22 13:22:00', 3800);
insert into readings_tab values (7, 'A', '2017-08-23 13:22:00', 2700);
insert into readings_tab values (8, 'B', '2017-08-23 13:22:00', 1630);
insert into readings_tab values (9, 'C', '2017-08-23 13:22:00', 3950);
insert into readings_tab values (10, 'A', '2017-08-24 13:22:00', 2850);
insert into readings_tab values (11, 'B', '2017-08-24 13:22:00', 1700);
insert into readings_tab values (12, 'C', '2017-08-24 13:22:00', 4200);
insert into readings_tab values (13, 'A', '2017-08-25 13:22:00', 3500);
insert into readings_tab values (14, 'B', '2017-08-25 13:22:00', 2300);
insert into readings_tab values (15, 'C', '2017-08-25 13:22:00', 4700);

当前查询:

select t.rownum, t.logged_on, t.tot_reading, coalesce(t.tot_reading - t3.tot_reading, 0) AS daily_generation
from 
  (
    select @rn:=@rn+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t, (SELECT @rn:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t 
  left join 
  (
    select @rn:=@rn+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t, (SELECT @rn:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t3 on t.rownum = t3.rownum + 1
  order by t.logged_on desc;

我期待低于输出。我不需要结果集中的公式(3500+2300+4700，等等...)。只是包含它以使其易于理解。

-----------------------------------------------------------------
| logged_on  |      tot_reading         |    daily_generation   |
-----------------------------------------------------------------
| 2017-08-25 | (3500+2300+4700) = 10500 | (10500 - 8750) = 1750 |
| 2017-08-24 | (2850+1700+4200) =  8750 |    (8750-8280) =  470 |
| 2017-08-23 | (2700+1630+3950) =  8280 |    (8280-7830) =  450 |
| 2017-08-22 | (2630+1400+3800) =  7830 |    (7830-7210) =  620 |
| 2017-08-21 | (2500+1210+3500) =  7210 |                     0 |
-----------------------------------------------------------------

我不明白为什么它没有产生预期的输出。有人可以帮忙吗？

Answer 1

如果使用变量，请确保它们对于每个子查询都是唯一的，否则您可能会得到不正确的结果。我建议使用以下调整后的查询(其中添加了一些列以帮助跟踪正在发生的事情):

select
      t.rownum, t.logged_on, t.tot_reading
    , coalesce(t.tot_reading - t3.tot_reading, 0) AS daily_generation
    , t3.rownum t3_rownum
    , t3.tot_reading t3_to_read
    , t.tot_reading  t_tot_read
from 
  (
    select @rn:=@rn+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t
    cross join (SELECT @rn:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t 
  left join 
  (
    select @rn2:=@rn2+1 AS rownum, date(t.logged_at) AS logged_on, sum(t.reading) AS tot_reading
    from readings_tab t
    cross join (SELECT @rn2:=0) t2
    group by date(t.logged_at)
    order by date(t.logged_at) desc
  ) t3 on t.rownum = t3.rownum + 1
  order by t.logged_on desc
  ;

请注意，我还建议使用明确的 CROSS JOIN 语法，因为它可以让需要维护此查询的任何人更容易理解。

这是结果(另见 http://sqlfiddle.com/#!9/dcb5e2/1)

| rownum |  logged_on | tot_reading | daily_generation | t3_rownum | t3_to_read | t_tot_read |
|--------|------------|-------------|------------------|-----------|------------|------------|
|      5 | 2017-08-25 |       10500 |             1750 |         4 |       8750 |      10500 |
|      4 | 2017-08-24 |        8750 |              470 |         3 |       8280 |       8750 |
|      3 | 2017-08-23 |        8280 |              450 |         2 |       7830 |       8280 |
|      2 | 2017-08-22 |        7830 |              620 |         1 |       7210 |       7830 |
|      1 | 2017-08-21 |        7210 |                0 |    (null) |     (null) |       7210 |