sql - Oracle 获取最近 10 天的 sql

Question

我需要从现在开始的最后 10 天我目前有以下 Oracle sql 查询来获取最近 10 个日期。

WITH DATES AS (
  SELECT sysdate - 10 AS value_date FROM dual
  UNION
  SELECT sysdate - 9  AS value_date FROM dual
  UNION
  SELECT sysdate - 8  AS value_date FROM dual
  UNION
  SELECT sysdate - 7  AS value_date FROM dual
  UNION
  SELECT sysdate - 6  AS value_date FROM dual
  UNION
  SELECT sysdate - 5  AS value_date FROM dual
  UNION
  SELECT sysdate - 4  AS value_date FROM dual
  UNION
  SELECT sysdate - 3  AS value_date FROM dual
  UNION
  SELECT sysdate - 2  AS value_date FROM dual
  UNION
  SELECT sysdate - 1  AS value_date FROM dual
  UNION
  SELECT sysdate      AS value_date FROM dual
  UNION
  SELECT sysdate + 1  AS value_date FROM dual

但我想要一种更合适、更有效的方法来获取过去 10 个日期。联合查询看起来很脏。我如何在 Oracle SQL 中执行此操作？

Answer 1

使用更紧凑的 CONNECT BY 子句:

SELECT trunc( sysdate ) - level + 2
FROM dual
CONNECT BY rownum <= 12

演示:http://sqlfiddle.com/#!4/a50e25/5

| TRUNC(SYSDATE)-LEVEL+2 |
|------------------------|
|   2017-09-15T00:00:00Z |
|   2017-09-14T00:00:00Z |
|   2017-09-13T00:00:00Z |
|   2017-09-12T00:00:00Z |
|   2017-09-11T00:00:00Z |
|   2017-09-10T00:00:00Z |
|   2017-09-09T00:00:00Z |
|   2017-09-08T00:00:00Z |
|   2017-09-07T00:00:00Z |
|   2017-09-06T00:00:00Z |
|   2017-09-05T00:00:00Z |
|   2017-09-04T00:00:00Z |