欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

oracle sql语句锦集

//查询出数据没有的话就补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

oracle sql语句锦集

//列转行并且时间按从小打到排序

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语句锦集