问题描述

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

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

解决方案

感觉只要改下日期的转换格式就没问题了。如果结果不行，你来咬我啊

解决方案二：
查找MySQL中查询慢的SQL语句的方法