新手请教一个sql查询问题,在线等,谢谢

Question

一张激活表TL_CSU_ACTIVATE_LOG:

一张用户表TV_CSU_USER

现在要查询2014-12to2015-12每月注册的不同类型(T,B,M,D)用户数,如下图:

sql 应该怎么写

Answer 1

参考Oracle行列转换
因为你的类型是固定的，所以直接查询，如果你的类型不固定，可以参考上面的链接。

SELECT TT.Month
    ,SUM(CASE WHEN TT.TYPE = 'B' THEN Count ELSE 0 END) AS B
    ,SUM(CASE WHEN TT.TYPE = 'M' THEN Count ELSE 0 END) AS M
    ,SUM(CASE WHEN TT.TYPE = 'D' THEN Count ELSE 0 END) AS D
    ,SUM(CASE WHEN TT.TYPE = 'T' THEN Count ELSE 0 END) AS T
FROM (
SELECT TO_CHAR(T1.ACTIVATE_TIME, 'yyyy-MM') AS Month
        , T2.TYPE
        , COUNT(*) AS Count
      FROM TL_CSU_ACTIVATE_LOG T1
      LEFT JOIN TV_CSU_USER T2 ON T2.USER_ID = T1.USER_ID
      WHERE TO_CHAR(T1.ACTIVATE_TIME, 'yyyy-MM') >= '2014-12' AND TO_CHAR(T1.ACTIVATE_TIME, 'yyyy-MM') <= '2015-12'
GROUP BY T2.TYPE
) TT
GROUP BY TT.Month
ORDER BY TT.Month