mysql - 更新先前的值并将 null 替换为先前的值

Question

请找到以下场景，对先前的值求和并将零替换为先前的值。

Create table joborder (
   jo_no       int(10),
   jo_date     datetime,
   jo_status   char(10)
);

insert into joborder values( 1,'01-03-13','A');    
insert into joborder values( 2,'01-03-13','A');    
Insert into joborder values (3,'01-03-13','A');    
insert into joborder values( 4,'04-03-13','A');    
insert into joborder values(5,'08-03-13','B');    
insert into joborder values( 6,'12-03-13','C');

状态:

A 表示已完成

B 表示进度

C 表示 Dev

我想要一份如下报告

Date         TotalJoborder    A      B     C
---------------------------------------------    
01-03-13      3               3      0     0    
04-03-13      1               0      1     0    
08-03-13      1               0      1     0    
12-03-13      1               0      0     1

我想要如下的报告应该增加以前的值并显示以前的值。

Date         TotalJoborder    A      B     C
---------------------------------------------    
01-03-13      3               3      0     0    
04-03-13      4               3      1     0    
08-03-13      5               3      1     1    
12-03-13      6               4      1     1

Answer 1

对于第一个输出，您可以执行以下操作:

select jo_date, 
       count(*) as total_job_order,
       sum(case when jo_status= 'A' then 1 else 0 end) as status_a,
       sum(case when jo_status= 'B' then 1 else 0 end) as status_b,
       sum(case when jo_status= 'C' then 1 else 0 end) as status_c
from joborder
group by jo_date
order by jo_date;

SQLFiddle:http://sqlfiddle.com/#!2/5ab191/1

对于“运行总计”，您可以执行以下操作:

select jo_date,
       (select count(*) from joborder j2 where j2.jo_date <= t1.jo_date) as running_sum,
       status_b,
       status_c
from (
  select jo_date, 
         sum(case when jo_status= 'A' then 1 else 0 end) as status_a,
         sum(case when jo_status= 'B' then 1 else 0 end) as status_b,
         sum(case when jo_status= 'C' then 1 else 0 end) as status_c
  from joborder
  group by jo_date
  order by jo_date
) t1

顺便说一句:我几乎 100% 确定您不希望 jo_status 列使用 char(10)，但是 varchar(10) 相反。