qq_31422159 2015-12-17 01:48 采纳率: 0%
浏览 2267

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
  • 写回答

1条回答

  • _Matthew 2023-11-21 09:44
    关注
    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;
    
    评论

报告相同问题?

悬赏问题

  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面