Heim  >  Artikel  >  Datenbank  >  mysql的查询sql语句转换成oracle的sql

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

WBOY
WBOYOriginal
2016-06-06 09:35:081836Durchsuche

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

<code>            AND STR_TO_DATE(A.CREATE_DATE, '%Y-%m-%d') </code>
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn