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

Question

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

Answer 1

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
            ) AS SUBSCRIBE,
            MAX(
                CASE N.EVENT
                    WHEN 'unsubscribe' THEN COUNT
                    ELSE 0
                END
            ) AS UNSUBSCRIBE,
            0 AS ISBIND,
            0 AS UNBIND
        FROM
            (
                SELECT
                    C.CREATE_DATE,
                    C.EVENT,
                    COUNT(C.EVENT) AS COUNT
                FROM
                    (
                        SELECT
                            TO_DATE(X.CREATE_DATE, 'YYYY-MM-DD') 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
            ) 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
                    ) AS ISBIND,
                    MAX(
                        CASE B.ISBIND
                            WHEN 0 THEN B.COUNT
                            ELSE 0
                        END
                    ) AS UNBIND
                FROM
                    (
                        SELECT
                            TO_DATE(U.BINDTIME, 'YYYY-MM-DD') AS BINDTIME,
                            U.ISBIND,
                            COUNT(U.ISBIND) AS COUNT
                        FROM
                            tbl_wx_mp_user U
                        WHERE
                            U.ISBIND IS NOT NULL
                        GROUP BY
                            TO_DATE(U.BINDTIME, 'YYYY-MM-DD'),
                            U.ISBIND
                    ) B
                GROUP BY
                    B.BINDTIME
            ) B
    ) A
WHERE
    1 = 1
    AND A.CREATE_DATE >= TO_DATE('2015-12-11', 'YYYY-MM-DD')
    AND TO_DATE(A.CREATE_DATE, 'YYYY-MM-DD') <= TO_DATE('2015-12-17', 'YYYY-MM-DD')
GROUP BY
    A.CREATE_DATE
ORDER BY
    A.CREATE_DATE DESC;