每月每一天的MySQL累计总数

Question

我需要一些有关此 SQL 运行总计的帮助。我已经尝试了一些建议，但还没有 100% 奏效，而且我的总数也不对。

查询显示每天生产的 gem 数量和克拉数，首先是超过 5 克拉的 gem ，然后是 5 克拉以下的 gem ，因此在日期的第一列之后，会出现这 4 列。

然后接下来的两列只需将 gem 加在一起和克拉加在一起作为每日总计。

之后，我想添加两列来创建 gem 和克拉的运行总计。

如有任何帮助，我们将不胜感激。谢谢!

select 
  `dbo_List_Dates`.`Full_Date` AS `Full_Date`,
  if(isnull(`qry_Register_Over5ct`.`StonesO5`),
    0,`qry_Register_Over5ct`.`StonesO5`) AS `StonesO5`,
  if(isnull(`qry_Register_Over5ct`.`CaratsO5`),
    0,format(`qry_Register_Over5ct`.`CaratsO5`,3)) AS `CaratsO5`,
  if(isnull(`qry_Register_Under5ct`.`StonesU5`),
    0,`qry_Register_Under5ct`.`StonesU5`) AS `StonesU5`,
  if(isnull(`qry_Register_Under5ct`.`CaratsU5`),
    0,format(`qry_Register_Under5ct`.`CaratsU5`,3)) AS `CaratsU5`,

  (if(isnull(`qry_Register_Over5ct`.`StonesO5`),
     0,`qry_Register_Over5ct`.`StonesO5`) +   if(isnull(`qry_Register_Under5ct`.`StonesU5`),
     0,`qry_Register_Under5ct`.`StonesU5`)) AS `Stones`,

  format((if(isnull(`qry_Register_Over5ct`.`CaratsO5`),
    0,`qry_Register_Over5ct`.`CaratsO5`) +    if(isnull(`qry_Register_Under5ct`.`CaratsU5`),
    0,`qry_Register_Under5ct`.`CaratsU5`)),3) AS `Carats`,

  date_format(`dbo_List_Dates`.`Full_Date`,'%Y-%m') AS `Date_Filter` 

from 
  (
   (`dbo_List_Dates` 
     left join `qry_Register_Over5ct`
     on((`dbo_List_Dates`.`Full_Date`=qry_Register_Over5ct`.`Shift_Date`))
    )
   left join `qry_Register_Under5ct` 
   on((`dbo_List_Dates`.`Full_Date`=`qry_Register_Under5ct`.`Shift_Date`))
  ) 
where 
  (date_format(`dbo_List_Dates`.`Full_Date`,'%Y-%m') = date_format(now(),'%Y-%m')) 
order by 
  `dbo_List_Dates`.`Full_Date`
limit 0,31

Answer 1

在mysql中运行总计需要使用变量。例如给定

create table register (id int, dt date, carats int);
insert into register values
(1,'2016-11-01',10),(2,'2016-11-01',10),(3,'2016-11-01',1),
(4,'2016-11-02',1),
(5,'2016-11-03',10),
(6,'2016-11-05',10),(7,'2016-11-05',1);

和这样的日期表

+------------+
| dte        |
+------------+
| 2016-11-01 |
| 2016-11-02 |
| 2016-11-03 |
| 2016-11-04 |
| 2016-11-05 |
+------------+

select  s.* ,@RunningTotal:=@RunningTotal + s.LT5_GE5 RunningTotal
from
(
select d.dte,
        sum(case when r.dt is not null and r.carats >= 5 then 1 else 0 end) GE5,
        sum(case when r.dt is not null and r.carats  <  5 then 1 else 0 end) LT5,
        sum(case when r.dt is not null and r.carats >= 5 then 1 else 0 end) +
        sum(case when r.dt is not null and r.carats  <  5 then 1 else 0 end) LT5_GE5
from  dates d
left join register r on r.dt = d.dte 
where  dte between  '2016-11-01' and '2016-11-05'
group by dte
) s ,(select @RunningTotal:=0) rt

结果

+------------+------+------+---------+--------------+
| dte        | GE5  | LT5  | LT5_GE5 | RunningTotal |
+------------+------+------+---------+--------------+
| 2016-11-01 |    2 |    1 |       3 |            3 |
| 2016-11-02 |    0 |    1 |       1 |            4 |
| 2016-11-03 |    1 |    0 |       1 |            5 |
| 2016-11-04 |    0 |    0 |       0 |            5 |
| 2016-11-05 |    1 |    1 |       2 |            7 |
+------------+------+------+---------+--------------+

如果你想要列总计，你可以在分组依据中包含一个汇总

select  s.* ,
            cast(if(dte is null,@RunningTotal,@RunningTotal:=@RunningTotal + s.LT5_GE5) as int) RunningTotal
from
(
select d.dte,
        sum(case when r.dt is not null and r.carats >= 5 then 1 else 0 end) GE5,
        sum(case when r.dt is not null and r.carats  <  5 then 1 else 0 end) LT5,
        sum(case when r.dt is not null and r.carats >= 5 then 1 else 0 end) +
        sum(case when r.dt is not null and r.carats  <  5 then 1 else 0 end) LT5_GE5
from  dates d
left join register r on r.dt = d.dte 
where  dte between  '2016-11-01' and '2016-11-05'
group by dte with rollup
) s ,(select @RunningTotal:=0) rt

结果

+------------+------+------+---------+--------------+
| dte        | GE5  | LT5  | LT5_GE5 | RunningTotal |
+------------+------+------+---------+--------------+
| 2016-11-01 |    2 |    1 |       3 |            3 |
| 2016-11-02 |    0 |    1 |       1 |            4 |
| 2016-11-03 |    1 |    0 |       1 |            5 |
| 2016-11-04 |    0 |    0 |       0 |            5 |
| 2016-11-05 |    1 |    1 |       2 |            7 |
| NULL       |    4 |    3 |       7 |            7 |
+------------+------+------+---------+--------------+
6 rows in set (0.02 sec)