统计记录

--统计1：领用和使用消费券情况select c.* from(select '已领取' as '消费券', count(a.id) as '数量'  from t1 aunion allselect '已领取并使用' as '消费券', count(b.id) as '数量'  from t1 b  where b.status=1)c--时段统计select '2016-07-02 00:00:00' as '开始时间','2016-07-03 23:59:59' as '结束时间',t.* from(select  '甲' as '绑定对象',  count(a.id) as '数量'  from  t1 a where LOWER(a.account_type)='jd' and a.enable_flag=1 and a.created_date between '2016-07-02 00:00:00' and '2016-07-03 23:59:59' unionselect '乙' as '绑定对象',  count(b.id) as '数量'  from  t1 b where LOWER(b.account_type)='alipay' and b.enable_flag=1 and b.created_date between '2016-07-02 00:00:00' and '2016-07-03 23:59:59' unionselect '丙' as '绑定对象',  count(c.id) as '数量'  from  t1 c where LOWER(c.account_type)='taobao' and c.enable_flag=1 and c.created_date between '2016-07-02 00:00:00' and '2016-07-03 23:59:59' ) t--统计2：绑定电商情况select t.* from(select '甲' as '绑定对象',  count(a.id) as '数量'  from  t1 a where LOWER(a.account_type)='jd' and a.enable_flag=1unionselect '乙' as '绑定对象',  count(b.id) as '数量'  from  t1 b where LOWER(b.account_type)='alipay' and b.enable_flag=1unionselect '丙' as '绑定对象',  count(c.id) as '数量'  from  t1 c where LOWER(c.account_type)='taobao' and c.enable_flag=1) t--统计2：根据用户类型统计电商绑定情况select t.* from(select 'Android用户' as '用户类型','甲' as '绑定对象',  count(a.id) as '数量' from t1 ainner join t2 bon a.customer_id=b.idinner join t3 con b.app_id=c.idwhere LOWER(a.account_type)='jd' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=0 and length(IFNULL(c.device_id,0))<20 union allselect 'Android用户' as '用户类型','乙' as '绑定对象',  count(a.id) as '数量' from t1 ainner join t2 bon a.customer_id=b.idinner join t3 con b.app_id=c.idwhere LOWER(a.account_type)='alipay' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=0 and length(IFNULL(c.device_id,0))<20 union allselect 'Android用户' as '用户类型','丙' as '绑定对象',  count(a.id) as '数量' from t1 ainner join t2 bon a.customer_id=b.idinner join t3 con b.app_id=c.idwhere LOWER(a.account_type)='taobao' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=0 and length(IFNULL(c.device_id,0))<20 union all  select 'IOS用户' as '用户类型','甲' as '绑定对象',  count(a.id) as '数量' from t1 ainner join t2 bon a.customer_id=b.idinner join t3 con b.app_id=c.idwhere LOWER(a.account_type)='jd' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=20 union allselect 'IOS用户' as '用户类型','乙' as '绑定对象',  count(a.id) as '数量' from t1 ainner join t2 bon a.customer_id=b.idinner join t3 con b.app_id=c.idwhere LOWER(a.account_type)='alipay' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=20 union allselect 'IOS用户' as '用户类型','丙' as '绑定对象',  count(a.id) as '数量' from t1 ainner join t2 bon a.customer_id=b.idinner join t3 con b.app_id=c.idwhere LOWER(a.account_type)='taobao' and a.enable_flag=1 and length(IFNULL(c.device_id,0))>=20 ) t--区分android和ios用户//iosselect  count(id) from t3 where length(IFNULL(device_id,0))>=20                                        --132367//androidselect  count(id) from t3 where length(IFNULL(device_id,0))>=1 and length(IFNULL(device_id,0))<20      --167454