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

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

作者：小涵 | 来源：互联网 | 2018-04-25 15:24

阅读: 6082

SELECTCREATE_DATE,SUM(ISBIND)ASISBIND,SUM(UNBIND)ASUNBIND,SUM(SUBSCRIBE)ASSUBSCRIBE,SUM(UNSUBSCRIBE)ASUNSUBSCRIBEFROM(SELECTN.CREATE_DATE,MAX(CASEN.`EVENT`WHEN'subscribe'THENCOUNTELSE0END)SUBSCRIBE,MAX(CASEN.`E

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

吐了个 "CAO" !

吐个槽吧,看都看了