在 Oracle 中即时构建数字表 [英] Build numbers table on the fly in Oracle

问题描述

如何根据当前日期返回包含过去四年的行集?

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

如果这个查询在 12/31/2010 运行，它应该返回:

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

2007
2008
2009
2010

但如果它在 1/1/2011 运行，它应该返回:

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

2008
2009
2010
2011

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

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;

但我不知道如何生成行来充实它.在 SQL Server 中，我将使用 CTE，如 此页面上的 fn_nums 函数.

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.

推荐答案

这是一种方法:

  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;

这篇关于在 Oracle 中即时构建数字表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！