代码之家 › 专栏 › 技术社区 › Kacper

多组分层查询

oracle sql

Kacper · 技术社区 · 6 年前

我想生成两个日期之间的每个月末日期,但我需要为多个组执行此操作。这里有一个简单的例子:

select last_day(add_months(date '2015-01-01', level - 1)), 1 gr from dual
connect by level <= 12
union all
select last_day(add_months(date '2015-01-01', level - 1)), 2 gr from dual
connect by level <= 12;

在没有 union all 因为我有很多小组。

我知道我可以 PL/SQL 但出于好奇,可以使用单个SQL语句吗?

我想要这样的查询:

with d as (
select date '2015-01-01' start_date, date '2015-12-01' end_date, 1 gr from dual
union all 
select date '2015-01-01' start_date, date '2015-12-01' end_date, 2 gr from dual
)
select last_day(add_months(start_date, level - 1)) from d
start with start_date = date '2015-01-01'
connect by level <= months_between(end_date, start_date);

但是作为第一个查询生成结果并不是 cross join

2 回复 | 直到 6 年前

Kaushik Nayak 6 年前

你可以使用 PRIOR 和 SYS_GUID() 选项

WITH d 
     AS (SELECT DATE '2015-01-01' start_date, 
                DATE '2015-12-01' end_date, 
                1                 gr 
         FROM   dual 
         UNION ALL 
         SELECT DATE '2015-01-01' start_date, 
                DATE '2015-12-01' end_date, 
                2                 gr 
         FROM   dual) 
SELECT gr, 
       last_day(add_months(start_date, LEVEL - 1)) AS dt 
FROM   d 
START WITH start_date = DATE '2015-01-01' 
CONNECT BY LEVEL <= months_between(end_date, start_date) 
           AND PRIOR gr = gr 
                     AND PRIOR sys_guid() IS NOT NULL 
ORDER  BY gr, 
          dt 

| GR |                   DT |
|----|----------------------|
|  1 | 2015-01-31T00:00:00Z |
|  1 | 2015-02-28T00:00:00Z |
|  1 | 2015-03-31T00:00:00Z |
|  1 | 2015-04-30T00:00:00Z |
|  1 | 2015-05-31T00:00:00Z |
|  1 | 2015-06-30T00:00:00Z |
|  1 | 2015-07-31T00:00:00Z |
|  1 | 2015-08-31T00:00:00Z |
|  1 | 2015-09-30T00:00:00Z |
|  1 | 2015-10-31T00:00:00Z |
|  1 | 2015-11-30T00:00:00Z |
|  2 | 2015-01-31T00:00:00Z |
|  2 | 2015-02-28T00:00:00Z |
|  2 | 2015-03-31T00:00:00Z |
|  2 | 2015-04-30T00:00:00Z |
|  2 | 2015-05-31T00:00:00Z |
|  2 | 2015-06-30T00:00:00Z |
|  2 | 2015-07-31T00:00:00Z |
|  2 | 2015-08-31T00:00:00Z |
|  2 | 2015-09-30T00:00:00Z |
|  2 | 2015-10-31T00:00:00Z |
|  2 | 2015-11-30T00:00:00Z |

Demo

Kacper 6 年前

我又找到了一条路,但是 lateral 也不是我的目标。Kaushik的答案就是我想要的。

with data as (
    select date '2015-01-01' start_date, date '2015-12-01' end_date, 1 as gr from dual
    union all
    select date '2015-01-01' start_date, date '2015-12-01' end_date, 2 as gr from dual
),
data_level as(
    select start_Date
        ,end_date
        ,gr
        ,months_between(end_date,start_date) + 1 as lvl
    from data)
select g from data_level,
lateral(select last_day(add_months(start_date,level - 1)) g
        from dual 
        connect by level <= lvl
);