当统计多条的三个参数在不同时间段的数据的sum,又只能写在同一个sql上时,可以考虑union all三次查询,
select * from
(
select kk.a_time as dates,kk.flag
,sum(kk.`参数一`) 参数一之和
,sum(kk.`参数二`) 参数二之和
,sum(kk.`参数三`) 参数三之和
from
(select
k.a_time
,k.flag
,sum(case when p_time=0 or p_time= a_time then 1 else 0 end) as '参数一'
,'' as '参数二'
,'' as '参数三'
from
(
select
a.pay_id
,o_days
,FROM_UNIXTIME(a_time/1000,'%Y-%m-%d')a_time
,case when p_time<>0 then FROM_UNIXTIME(p_time/1000,'%Y-%m-%d') else 0 end as p_time
,case when mod(a.pay_id,2)=0 and l.pay_id is not null then '系统一' else '系统二' end as flag
from pay a
LEFT JOIN
log l on a.pay_id = l.pay_id and l.`status` = 1 and type = 'baseinfo'
where a_time>= UNIX_TIMESTAMP('2018-07-23')*1000 and a_time< UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 1 DAY))*1000 +24*3600*1000
)k
GROUP BY 1,2
UNION all
select
DATE_SUB(k.a_time,INTERVAL 1 day) a_time
,k.flag
,'' as '参数一'
,'' as '参数二'
,sum(case when p_time=0 or p_time = DATE_SUB(a_time,INTERVAL 1 DAY) or p_time= a_time then 1 else 0 end) as '参数三'
from
(
select
a.pay_id
,o_days
,FROM_UNIXTIME(a_time/1000,'%Y-%m-%d')a_time
,case when p_time<>0 then FROM_UNIXTIME(p_time/1000,'%Y-%m-%d') else 0 end as p_time
,case when mod(a.pay_id,2)=0 and l.pay_id is not null then '系统一' else '系统二' end as flag
from pay a
LEFT JOIN
log l on a.pay_id = l.pay_id and l.`status` = 1 and type = 'baseinfo'
where a_time>= UNIX_TIMESTAMP('2018-07-24')*1000 and a_time< UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 1 DAY))*1000 +24*3600*1000
)k
GROUP BY 1,2
union all
select
DATE_SUB(k.a_time,INTERVAL 1 day) a_time
,k.flag
,'' as '参数一'
,sum(case when o_days>0 then 1 else 0 end) as '参数二'
,'' as '参数三'
from
(
select
a.pay_id
,o_days
,FROM_UNIXTIME(a_time/1000,'%Y-%m-%d')a_time
,case when p_time<>0 then FROM_UNIXTIME(p_time/1000,'%Y-%m-%d') else 0 end as p_time
,case when mod(a.pay_id,2)=0 and l.pay_id is not null then '系统一' else '系统二' end as flag
from pay a
LEFT JOIN
log l on a.pay_id = l.pay_id and l.`status` = 1 and type = 'baseinfo'
where a_time>= UNIX_TIMESTAMP('2018-07-24')*1000 and a_time< UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL 1 DAY))*1000 +24*3600*1000
)k
GROUP BY 1,2
)kk
GROUP BY 1,2) kk
-- where dates>={DATE1} and dates<={DATE2}
该语句用了三次统计分别统计 参数一,参数二,参数三 的对应记录的dates,再做合并,有点好性能,只为满足需求的无奈之举!