列出两个月份之间所有的月份
在这里插入代码片SELECT
TO_CHAR( ADD_MONTHS( TO_DATE( '201804', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS monthlist
FROM
DUAL CONNECT BY ROWNUM <= months_between(
to_date( '201809', 'yyyyMM' ),
to_date( '201801', 'yyyyMM' ))+1
//查询出数据没有的话就补0
在这里插入代码片select a.monthlist,nvl(b.ENTRY_COUNT,0) as AAA from
(SELECT
TO_CHAR( ADD_MONTHS( TO_DATE( '201904', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS monthlist
FROM
DUAL CONNECT BY ROWNUM <= months_between(
to_date( '201912', 'yyyyMM' ),
to_date( '201904', 'yyyyMM' ))+1) a
LEFT JOIN (SELECT
TIME,
SUM (ENTRY_COUNT) ENTRY_COUNT
FROM
(
SELECT
A . TIME,
SUM (A .ENTRY_COUNT) ENTRY_COUNT
FROM
EXP_HOT_STATISTICS A
WHERE
1 = 1
AND "SUBSTR" (A . TIME, 0, 6) BETWEEN '201903'
AND '201912'
AND (
KEY_WORD_TYPE LIKE '%奶粉%'
)
AND A .KEY_WORD_STATUS = '1'
GROUP BY
A . TIME
ORDER BY
ENTRY_COUNT DESC
)
GROUP BY
TIME
ORDER BY
TIME ASC) b
on a.MONTHLIST =b.TIME
ORDER BY MONTHLIST
//列转行并且时间按从小打到排序
SELECT
listagg (TIME, ',') WITHIN GROUP (ORDER BY TIME ASC) TIME,
listagg (ENTRY_COUNT, ',') WITHIN GROUP (ORDER BY TIME ASC) ENTRY_COUNT_NUMBER,
KEY_WORD_TYPE
FROM
(
SELECT
A . TIME AS TIME,
A .KEY_WORD_TYPE,
'' || SUM (A .ENTRY_COUNT) ENTRY_COUNT
FROM
EXP_HOT_STATISTICS A
WHERE
1 = 1
AND "SUBSTR" (A . TIME, 0, 6) BETWEEN '201902'
AND '201911'
AND (
KEY_WORD_TYPE LIKE '%奶粉%'
OR KEY_WORD_TYPE LIKE '%运动鞋%'
)
AND A .KEY_WORD_STATUS = '1'
GROUP BY
A .KEY_WORD_TYPE,
A . TIME
)
GROUP BY
KEY_WORD_TYPE