脚本如下:
[code=sql] select t5.store_id 站点ID
,t5.phone 负责人手机号码
,t5.store_name 站点名称
,t5.ec_name 快递品牌
,t5.college_name 归属校区
,t4.phonesum 累计总数
,t4.newadd 新增数
from
(select t2.store_id
,t2.phonesum
,t3.newadd
from
(select a.store_id,
count(distinct a.phone) phonesum
from tb_sms a,(select phone,
min(submit_time) as submit_time
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time < '2016-06-07 23:59:59'
group by phone) b
where a.phone=b.phone
and a.submit_time=b.submit_time
and a.message_type_id = 'p_noticetype_come'
and a.submit_time < '2016-06-07 23:59:59'
group by a.store_id) t2
left join
(select a.store_id
,count(distinct a.phone) newadd
from tb_sms a,(select phone,
min(submit_time) as submit_time
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time between '2016-06-07 0:00:00'
and '2016-06-07 23:59:59'
group by phone) b
where a.phone=b.phone
and a.submit_time=b.submit_time
and a.phone not in (select phone
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time < '2016-06-06 23:59:59')
and a.message_type_id = 'p_noticetype_come'
and a.submit_time between '2016-06-07 0:00:00' and '2016-06-07 23:59:59'
group by a.store_id) t3
on t2.store_id=t3.store_id
group by t2.store_id) t4
left join
(select a.store_id
,d.phone,a.store_name
,group_concat(distinct c.full_name) college_name
,group_concat(distinct f.full_name) ec_name
from tb_store a left join tb_store_college_rel b
on a.store_id = b.store_id
left join tb_college c
on b.college_id=c.college_id
left join tb_store_exp d
on a.store_id=d.sto_exp_id
left join tb_store_ec_rel e
on a.store_id=e.store_id
left join tb_express_company f
on e.express_company_id=f.express_company_id
group by a.store_id,d.phone,a.store_name) t5
on t4.store_id=t5.store_id;
/code]我想执行出4.7日到6.7日每天的结果,并导出excel,请问有什么好的方法提供,不知道怎么搜~~~~~~~~
[code=sql] select t5.store_id 站点ID
,t5.phone 负责人手机号码
,t5.store_name 站点名称
,t5.ec_name 快递品牌
,t5.college_name 归属校区
,t4.phonesum 累计总数
,t4.newadd 新增数
from
(select t2.store_id
,t2.phonesum
,t3.newadd
from
(select a.store_id,
count(distinct a.phone) phonesum
from tb_sms a,(select phone,
min(submit_time) as submit_time
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time < '2016-06-07 23:59:59'
group by phone) b
where a.phone=b.phone
and a.submit_time=b.submit_time
and a.message_type_id = 'p_noticetype_come'
and a.submit_time < '2016-06-07 23:59:59'
group by a.store_id) t2
left join
(select a.store_id
,count(distinct a.phone) newadd
from tb_sms a,(select phone,
min(submit_time) as submit_time
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time between '2016-06-07 0:00:00'
and '2016-06-07 23:59:59'
group by phone) b
where a.phone=b.phone
and a.submit_time=b.submit_time
and a.phone not in (select phone
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time < '2016-06-06 23:59:59')
and a.message_type_id = 'p_noticetype_come'
and a.submit_time between '2016-06-07 0:00:00' and '2016-06-07 23:59:59'
group by a.store_id) t3
on t2.store_id=t3.store_id
group by t2.store_id) t4
left join
(select a.store_id
,d.phone,a.store_name
,group_concat(distinct c.full_name) college_name
,group_concat(distinct f.full_name) ec_name
from tb_store a left join tb_store_college_rel b
on a.store_id = b.store_id
left join tb_college c
on b.college_id=c.college_id
left join tb_store_exp d
on a.store_id=d.sto_exp_id
left join tb_store_ec_rel e
on a.store_id=e.store_id
left join tb_express_company f
on e.express_company_id=f.express_company_id
group by a.store_id,d.phone,a.store_name) t5
on t4.store_id=t5.store_id;
/code]我想执行出4.7日到6.7日每天的结果,并导出excel,请问有什么好的方法提供,不知道怎么搜~~~~~~~~
,t5.phone 负责人手机号码
,t5.store_name 站点名称
,t5.ec_name 快递品牌
,t5.college_name 归属校区
,t4.phonesum 累计总数
,t4.newadd 新增数
from
(select t2.store_id
,t2.phonesum
,t3.newadd
from
(select a.store_id,
count(distinct a.phone) phonesum
from tb_sms a,(select phone,
min(submit_time) as submit_time
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time < '2016-06-07 23:59:59'
group by phone) b
where a.phone=b.phone
and a.submit_time=b.submit_time
and a.message_type_id = 'p_noticetype_come'
and a.submit_time < '2016-06-07 23:59:59'
group by a.store_id) t2
left join
(select a.store_id
,count(distinct a.phone) newadd
from tb_sms a,(select phone,
min(submit_time) as submit_time
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time between '2016-06-07 0:00:00'
and '2016-06-07 23:59:59'
group by phone) b
where a.phone=b.phone
and a.submit_time=b.submit_time
and a.phone not in (select phone
from tb_sms
where message_type_id = 'p_noticetype_come'
and submit_time < '2016-06-06 23:59:59')
and a.message_type_id = 'p_noticetype_come'
and a.submit_time between '2016-06-07 0:00:00' and '2016-06-07 23:59:59'
group by a.store_id) t3
on t2.store_id=t3.store_id
group by t2.store_id) t4
left join
(select a.store_id
,d.phone,a.store_name
,group_concat(distinct c.full_name) college_name
,group_concat(distinct f.full_name) ec_name
from tb_store a left join tb_store_college_rel b
on a.store_id = b.store_id
left join tb_college c
on b.college_id=c.college_id
left join tb_store_exp d
on a.store_id=d.sto_exp_id
left join tb_store_ec_rel e
on a.store_id=e.store_id
left join tb_express_company f
on e.express_company_id=f.express_company_id
group by a.store_id,d.phone,a.store_name) t5
on t4.store_id=t5.store_id;