oracle sql语句锦集
程序员文章站
2024-03-23 09:02:04
...
列出两个月份之间所有的月份
在这里插入代码片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
推荐阅读
-
oracle sql语句锦集
-
Oracle 常用性能监控SQL语句 博客分类: ORCLE ORCLE
-
使用Oracle的DBMS_SQL包执行动态SQL语句 博客分类: 数据库 SQLOracleCC++C#
-
oracle 使用sql语句向表中添加字段
-
Oracle PL/SQL 中if语句的应用 博客分类: 数据库 OraclePL/SQLif
-
一些 Oracle Sql 语句的使用 博客分类: DB开发 SQLOracleLinuxjava数据库
-
sql server、db2、oracle 存储过程动态sql语句示例 博客分类: 程序日志 ORACLE、DB2、SQL Server数据库存储过程动态SQL
-
取得表某个分组的最大值 博客分类: sql语句oracle
-
to_date() 时间相减 截取 博客分类: sql语句oracle
-
to_date() 时间相减 截取 博客分类: sql语句oracle