mysql的查询sql语句转换成oracle的sql

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')