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中如何实现这个功能？

- Sulejmani

---

你正在寻找类似于“VALUES”子句（http://modern-sql.com/feature/values）的东西，但是Oracle仍然不支持它。如果你喜欢的话，可以编写递归查询。比“UNION”查询更好。顺便说一下，它应该是“UNION ALL”，因为没有重复项需要删除——不要让DBMS做比必要更多的工作。 - Thorsten Kettner

Answer 1

使用带有CONNECT BY子句的更紧凑的等效语句：

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

Demo: 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 |

- krokodilko

Answer 2

您可以使用分层查询来获得与您的查询相同的结果（还可以使用TRUNC()将日期截断到当天午夜的开始）：

SELECT TRUNC( SYSDATE ) - 11 + LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 12

或者，使用递归子查询因式化子句：

WITH days ( dt ) AS (
  SELECT TRUNC( SYSDATE ) - 10 FROM DUAL
UNION ALL
  SELECT dt + 1 FROM days WHERE dt <= SYSDATE
)
SELECT dt FROM days

- MT0