mysqlsqloracle
select
create_date,
sum(isbind) as isbind,
sum(unbind) as unbind,
sum(subscribe) as subscribe,
sum(unsubscribe) as unsubscribe
from
(
select
n.create_date,
max(
case n.event
when 'subscribe' then
count
else
0
end
) subscribe,
max(
case n.event
when 'unsubscribe' then
count
else
0
end
) unsubscribe,
0 isbind,
0 unbind
from
(
select
c.create_date,
c.event,
count(c.event) count
from
(
select
str_to_date(x.create_date, '%y-%m-%d') as create_date,
x.event
from
tbl_wx_mp_xml_message x
where
x.msg_type = 'event'
and x.event in ('unsubscribe','subscribe')
) c
group by
c.create_date,
c.event
order by
c.event
) as n
where
n.create_date is not null
group by
n.create_date
union
select
*
from
(
select
b.bindtime as create_date,
0 as subscribe,
0 as unsubscribe,
max(
case b.isbind
when 1 then
b.count
else
0
end
) isbind,
max(
case b.isbind
when 0 then
b.count
else
0
end
) unbind
from
(
select
str_to_date(u.bindtime, '%y-%m-%d') as bindtime,
u.isbind,
count(u.isbind) count
from
tbl_wx_mp_user u
where
u.isbind is not null
group by
str_to_date(u.bindtime, '%y-%m-%d'),
u.isbind
) b
group by
b.bindtime
) b
) a
where
1 = 1
and a.create_date >= str_to_date('2015-12-11', '%y-%m-%d')
AND STR_TO_DATE(A.CREATE_DATE, '%Y-%m-%d') <= STR_TO_DATE('2015-12-17', '%Y-%m-%d') GROUP BY A.CREATE_DATE ORDER BY A.CREATE_DATE DESC 
