在 Oracle 中动态构建数字表

发布于 2024-10-09 07:53:37 字数 584 浏览 1 评论 0原文

如何根据当前日期返回由过去四年组成的行集？

如果此查询在 2010 年 12 月 31 日运行，则应返回：

但如果在 2011 年 1 月 1 日运行，则应返回：

这是我开始的内容，两个查询返回起始年份。我更喜欢第二种，因为转换为字符串对我来说有点脏。

SELECT TO_CHAR(TRUNC(sysdate, 'YY') - INTERVAL '3' YEAR, 'YYYY') FROM DUAL;
SELECT EXTRACT (YEAR FROM sysdate) - 3 FROM DUAL;

但我不知道如何生成行来充实这一点。在 SQL Server 中，我将使用 CTE，如本页的 fn_nums 函数。

原文

How do I return a rowset consisting of the last four years based on the current date?

If this query runs on 12/31/2010 it should return:

But if it is run on 1/1/2011 it should return:

Here's what I started with, two queries that return the starting year. I prefer the second as converting to string feels a bit dirty to me.

SELECT TO_CHAR(TRUNC(sysdate, 'YY') - INTERVAL '3' YEAR, 'YYYY') FROM DUAL;
SELECT EXTRACT (YEAR FROM sysdate) - 3 FROM DUAL;

But I don't know how to generate rows to flesh this out. In SQL Server I'd use a CTE as in the fn_nums function on this page.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南笙 2024-10-16 07:53:37

要显示 1 到 100 之间的数字：

SELECT  LEVEL 
FROM DUAL
CONNECT BY LEVEL <= 100
ORDER BY LEVEL

要更改最大值，请在第三行中更改它。

To display numbers from 1 to 100:

SELECT  LEVEL 
FROM DUAL
CONNECT BY LEVEL <= 100
ORDER BY LEVEL

To change the max, change it in the third line.

回复收藏 0 原文

欲拥i 2024-10-16 07:53:37

这是一种方法：

  SELECT yr
    FROM (    SELECT EXTRACT (YEAR FROM (ADD_MONTHS ( SYSDATE, - ( (LEVEL - 1) * 12)))) yr
                FROM DUAL
          CONNECT BY LEVEL <= 4)
ORDER BY yr;

或：

  SELECT yr
    FROM (    SELECT  EXTRACT (YEAR FROM sysdate) - (level -1 ) yr
                FROM DUAL
          CONNECT BY LEVEL <= 4)
ORDER BY yr;

或：

    SELECT yr
      FROM (SELECT EXTRACT (YEAR FROM SYSDATE) - (x - 1) yr
              FROM DUAL
            MODEL
               DIMENSION BY (1 AS z)
               MEASURES (1 x)
               RULES
                  ITERATE (4)
                  (x [ITERATION_NUMBER] = ITERATION_NUMBER + 1))
  ORDER BY yr;

Here is one way:

  SELECT yr
    FROM (    SELECT EXTRACT (YEAR FROM (ADD_MONTHS ( SYSDATE, - ( (LEVEL - 1) * 12)))) yr
                FROM DUAL
          CONNECT BY LEVEL <= 4)
ORDER BY yr;

Or:

  SELECT yr
    FROM (    SELECT  EXTRACT (YEAR FROM sysdate) - (level -1 ) yr
                FROM DUAL
          CONNECT BY LEVEL <= 4)
ORDER BY yr;

Or:

    SELECT yr
      FROM (SELECT EXTRACT (YEAR FROM SYSDATE) - (x - 1) yr
              FROM DUAL
            MODEL
               DIMENSION BY (1 AS z)
               MEASURES (1 x)
               RULES
                  ITERATE (4)
                  (x [ITERATION_NUMBER] = ITERATION_NUMBER + 1))
  ORDER BY yr;

回复收藏 0 原文

美人如玉 2024-10-16 07:53:37

与接受的答案类似，您可以用 with 子句替换内联视图。我发现 with 子句更具可读性。特别是如果您要作为同一查询的一部分对动态 NUMBERS 表执行多个计算 - 整个 SQL 语句更具可读性。

动态数字表：

WITH NUMBERS_START_AT_ZERO AS
     (SELECT LEVEL - 1 AS NUM 
       FROM DUAL
       CONNECT BY LEVEL <= 4
       order by NUM desc)
SELECT  EXTRACT (YEAR FROM sysdate) - NUM AS YEARS
  FROM NUMBERS_START_AT_ZERO

输出：

Similar to the accepted answer you can replace the inline view with a with clause. I find the with clause more readable. In particular if you are going to perform multiple calculations against the on-the-fly NUMBERS table as part of the same query - the entire SQL statement is more readable.

Numbers table on the fly:

WITH NUMBERS_START_AT_ZERO AS
     (SELECT LEVEL - 1 AS NUM 
       FROM DUAL
       CONNECT BY LEVEL <= 4
       order by NUM desc)
SELECT  EXTRACT (YEAR FROM sysdate) - NUM AS YEARS
  FROM NUMBERS_START_AT_ZERO

Output: