如果我理解正确(您希望有一个会议在开始日期和结束日期之间应该发生的日期列表),那么我认为以下查询应该适合您:
WITH ssrmeet AS (SELECT 32789 ssrmeet_crn, 201730 ssrmeet_term_code, to_date('01/03/2017', 'dd/mm/yyyy') ssrmeet_start_date, to_date('01/04/2017', 'dd/mm/yyyy') ssrmeet_end_date, NULL sun_day, NULL mon_day, 'T' tue_day, 'W' wed_day, NULL thu_day, 'F' fri_day, 'S' sat_day FROM dual UNION ALL
SELECT 32790 ssrmeet_crn, 201730 ssrmeet_term_code, to_date('05/03/2017', 'dd/mm/yyyy') ssrmeet_start_date, to_date('27/03/2017', 'dd/mm/yyyy') ssrmeet_end_date, NULL sun_day, NULL mon_day, 'T' tue_day, 'W' wed_day, NULL thu_day, 'F' fri_day, 'S' sat_day FROM dual)
SELECT ssrmeet_crn,
ssrmeet_start_date,
ssrmeet_end_date,
dt,
to_char(dt, 'Dy', 'nls_date_language = english') dt_day,
CASE WHEN (to_char(dt, 'Dy', 'nls_date_language = english') = 'Mon' AND mon_day = 'M')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Tue' AND tue_day = 'T')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Wed' AND wed_day = 'W')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Thu' AND thu_day = 'R')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Fri' AND fri_day = 'F')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Sat' AND sat_day = 'S')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Sun' AND sun_day = 'U')
THEN dt
END meet_dt
FROM (SELECT ssrmeet_crn,
ssrmeet_start_date,
ssrmeet_end_date,
ssrmeet_start_date - 1 + LEVEL dt,
sun_day,
mon_day,
tue_day,
wed_day,
thu_day,
fri_day,
sat_day
FROM ssrmeet
CONNECT BY ssrmeet_crn = PRIOR ssrmeet_crn
AND ssrmeet_term_code = PRIOR ssrmeet_term_code
AND PRIOR sys_guid() IS NOT NULL
AND LEVEL <= ssrmeet_end_date - ssrmeet_start_date + 1)
WHERE CASE WHEN (to_char(dt, 'Dy', 'nls_date_language = english') = 'Mon' AND mon_day = 'M')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Tue' AND tue_day = 'T')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Wed' AND wed_day = 'W')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Thu' AND thu_day = 'R')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Fri' AND fri_day = 'F')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Sat' AND sat_day = 'S')
OR (to_char(dt, 'Dy', 'nls_date_language = english') = 'Sun' AND sun_day = 'U')
THEN dt
END IS NOT NULL
ORDER BY ssrmeet_crn,
ssrmeet_start_date,
dt;
SSRMEET_CRN SSRMEET_START_DATE SSRMEET_END_DATE DT DT_DAY MEET_DT
----------- ------------------ ---------------- ----------- ------ -----------
32789 01/03/2017 01/04/2017 01/03/2017 Wed 01/03/2017
32789 01/03/2017 01/04/2017 03/03/2017 Fri 03/03/2017
32789 01/03/2017 01/04/2017 04/03/2017 Sat 04/03/2017
32789 01/03/2017 01/04/2017 07/03/2017 Tue 07/03/2017
32789 01/03/2017 01/04/2017 08/03/2017 Wed 08/03/2017
32789 01/03/2017 01/04/2017 10/03/2017 Fri 10/03/2017
32789 01/03/2017 01/04/2017 11/03/2017 Sat 11/03/2017
32789 01/03/2017 01/04/2017 14/03/2017 Tue 14/03/2017
32789 01/03/2017 01/04/2017 15/03/2017 Wed 15/03/2017
32789 01/03/2017 01/04/2017 17/03/2017 Fri 17/03/2017
32789 01/03/2017 01/04/2017 18/03/2017 Sat 18/03/2017
32789 01/03/2017 01/04/2017 21/03/2017 Tue 21/03/2017
32789 01/03/2017 01/04/2017 22/03/2017 Wed 22/03/2017
32789 01/03/2017 01/04/2017 24/03/2017 Fri 24/03/2017
32789 01/03/2017 01/04/2017 25/03/2017 Sat 25/03/2017
32789 01/03/2017 01/04/2017 28/03/2017 Tue 28/03/2017
32789 01/03/2017 01/04/2017 29/03/2017 Wed 29/03/2017
32789 01/03/2017 01/04/2017 31/03/2017 Fri 31/03/2017
32789 01/03/2017 01/04/2017 01/04/2017 Sat 01/04/2017
32790 05/03/2017 27/03/2017 07/03/2017 Tue 07/03/2017
32790 05/03/2017 27/03/2017 08/03/2017 Wed 08/03/2017
32790 05/03/2017 27/03/2017 10/03/2017 Fri 10/03/2017
32790 05/03/2017 27/03/2017 11/03/2017 Sat 11/03/2017
32790 05/03/2017 27/03/2017 14/03/2017 Tue 14/03/2017
32790 05/03/2017 27/03/2017 15/03/2017 Wed 15/03/2017
32790 05/03/2017 27/03/2017 17/03/2017 Fri 17/03/2017
32790 05/03/2017 27/03/2017 18/03/2017 Sat 18/03/2017
32790 05/03/2017 27/03/2017 21/03/2017 Tue 21/03/2017
32790 05/03/2017 27/03/2017 22/03/2017 Wed 22/03/2017
32790 05/03/2017 27/03/2017 24/03/2017 Fri 24/03/2017
32790 05/03/2017 27/03/2017 25/03/2017 Sat 25/03/2017
这首先需要执行分层查询,为开始日期和结束日期之间的每个日期生成一行(添加1以在结果中包含start\u日期)。如果有多行,这将为每行生成分层查询。如果希望一次只对一行执行此操作,则只需要connect by子句中的最后一行(前三行是为每行创建层次结构所必需的,而不是跨行集合。我假设SSRMETE\U term\U代码SSRMETE\U crn是唯一的)。
一旦有了此列表,就可以检查日期标志,查看日期是否为标记的日期之一,如果不是,就不要输出它。(注意,由于您没有提供任何示例数据或预期输出,我不得不猜测表的内容和预期输出。)
如果您有机会更改设计,我会在day flag列中使用一个简单的Y/N(或Y/null),而不是每个列都有自己的标志(即mon\u flag=Y,tue\u flag=Y等,而不是mon\u flag='M',tue\u flag='T'等)
