获取每个Unique ID对应起止日期间所有周末的SQL查询求助

问题根源 #

你的层级查询出现循环错误，核心原因是CONNECT BY prior Unique_id = Unique_id会让同一Unique_id的所有行互相引用，触发无限递归循环。要解决这个问题，必须确保每行仅以自身作为父行，避免跨行递归，同时正确生成日期序列。

修正后的测试查询 #

with req_val(Unique_id,start_date,end_Date) as
(select '105000182','20240710','20240810' from dual
 union all
 select '105000871','20240806','20240906' from dual
 union all
 select '105001602','20240726','20240826' from dual
 union all
 select '105002582','20240727','20240827' from dual)
select Unique_id,
       start_date,
       end_Date,
       trunc(to_date(START_DATE,'yyyymmdd') + level - 1) as weekend_date
from req_val
where trunc(to_date(start_Date,'yyyymmdd') + level - 1) - trunc(to_date(start_date,'yyyymmdd') + level - 1,'IW') > 5
connect by prior Unique_id = Unique_id
       and prior rowid = rowid  -- 强制父行仅为当前行，彻底避免循环
       and to_date(start_date,'yyyymmdd') + level - 1 <= to_date(end_date,'yyyymmdd')

关键修改说明 #

1. 添加prior rowid = rowid：ROWID是表中每行的唯一标识符，确保递归时父行只能是当前行本身，完全阻断同Unique_id行之间的循环递归。
2. 调整日期计算逻辑：将+ level改为+ level - 1，确保包含Start Date当天（如果当天是周末），符合"日期区间内"的需求。
3. 优化递归终止条件：将日期范围判断移至CONNECT BY中，提前终止不必要的递归计算。

针对实际表TABLE1的查询语句 #

SELECT UNIQUE_ID,
       START_DATE,
       END_DATE,
       trunc(to_date(START_DATE,'yyyymmdd') + level - 1) AS WEEKEND_DATE
FROM TABLE1
WHERE trunc(to_date(START_DATE,'yyyymmdd') + level - 1) - trunc(to_date(START_DATE,'yyyymmdd') + level - 1,'IW') > 5
CONNECT BY PRIOR UNIQUE_ID = UNIQUE_ID
       AND PRIOR ROWID = ROWID
       AND to_date(START_DATE,'yyyymmdd') + level - 1 <= to_date(END_DATE,'yyyymmdd')

替代方案（用SYS_GUID()避免循环） #

也可以用PRIOR SYS_GUID() IS NOT NULL替代PRIOR ROWID = ROWID，效果一致：

with req_val(Unique_id,start_date,end_Date) as
(select '105000182','20240710','20240810' from dual
 union all
 select '105000871','20240806','20240906' from dual
 union all
 select '105001602','20240726','20240826' from dual
 union all
 select '105002582','20240727','20240827' from dual)
select Unique_id,
       start_date,
       end_Date,
       trunc(to_date(START_DATE,'yyyymmdd') + level - 1) as weekend_date
from req_val
where trunc(to_date(start_Date,'yyyymmdd') + level - 1) - trunc(to_date(start_date,'yyyymmdd') + level - 1,'IW') > 5
connect by prior Unique_id = Unique_id
       and prior sys_guid() is not null
       and to_date(start_date,'yyyymmdd') + level - 1 <= to_date(end_date,'yyyymmdd')

内容的提问来源于stack exchange，提问作者Learncoholic