我有一个包含以下列的表
select a.ssrmeet_crn
,a.ssrmeet_begin_time
,a.ssrmeet_end_time
,a.ssrmeet_start_date
,a.ssrmeet_end_date
,a.ssrmeet_sun_day
,a.ssrmeet_mon_day
,a.ssrmeet_tue_day
,a.ssrmeet_wed_day
,a.ssrmeet_thu_day
,a.ssrmeet_fri_day
,a.ssrmeet_sat_day
from ssrmeet a
where a.ssrmeet_term_code = 201730
and a.ssrmeet_crn = 32789
一个班级的行看起来像这样
SSRMEET_CRN SSRMEET_BEGIN_TIME SSRMEET_END_TIME SSRMEET_START_DATE SSRMEET_END_DATE SSRMEET_SUN_DAY SSRMEET_MON_DAY SSRMEET_TUE_DAY SSRMEET_WED_DAY SSRMEET_THU_DAY SSRMEET_FRI_DAY SSRMEET_SAT_DAY
32789 1000 1050 1/8/2018 12:00:00 AM 5/4/2018 12:00:00 AM M W F
我需要它列出每个班级的会议
CRN START_DATE END_DATE
32789 1/15/2018 10:00:00 AM 1/15/2018 10:50:00 AM
32789 1/22/2018 10:00:00 AM 1/22/2018 10:50:00 AM
32789 1/29/2018 10:00:00 AM 1/29/2018 10:50:00 AM
32789 2/05/2018 10:00:00 AM 2/05/2018 10:50:00 AM
32789 2/12/2018 10:00:00 AM 2/12/2018 10:50:00 AM
32789 2/19/2018 10:00:00 AM 2/19/2018 10:50:00 AM
32789 2/26/2018 10:00:00 AM 2/26/2018 10:50:00 AM
32789 3/05/2018 10:00:00 AM 3/05/2018 10:50:00 AM
32789 3/12/2018 10:00:00 AM 3/12/2018 10:50:00 AM
32789 3/19/2018 10:00:00 AM 3/19/2018 10:50:00 AM
32789 3/26/2018 10:00:00 AM 3/26/2018 10:50:00 AM
32789 4/02/2018 10:00:00 AM 4/02/2018 10:50:00 AM
32789 4/09/2018 10:00:00 AM 4/09/2018 10:50:00 AM
32789 3/16/2018 10:00:00 AM 3/16/2018 10:50:00 AM
32789 4/23/2018 10:00:00 AM 4/23/2018 10:50:00 AM
32789 4/30/2018 10:00:00 AM 4/30/2018 10:50:00 AM
32789 1/10/2018 10:00:00 AM 1/10/2018 10:50:00 AM
32789 1/17/2018 10:00:00 AM 1/17/2018 10:50:00 AM
32789 1/24/2018 10:00:00 AM 1/24/2018 10:50:00 AM
32789 1/31/2018 10:00:00 AM 1/31/2018 10:50:00 AM
32789 2/07/2018 10:00:00 AM 2/07/2018 10:50:00 AM
32789 2/14/2018 10:00:00 AM 2/14/2018 10:50:00 AM
32789 2/21/2018 10:00:00 AM 2/21/2018 10:50:00 AM
2789 2/28/2018 10:00:00 AM 2/28/2018 10:50:00 AM
32789 3/07/2018 10:00:00 AM 3/07/2018 10:50:00 AM
32789 3/14/2018 10:00:00 AM 3/14/2018 10:50:00 AM
32789 3/21/2018 10:00:00 AM 3/21/2018 10:50:00 AM
32789 3/28/2018 10:00:00 AM 3/28/2018 10:50:00 AM
32789 4/04/2018 10:00:00 AM 4/04/2018 10:50:00 AM
32789 4/11/2018 10:00:00 AM 4/11/2018 10:50:00 AM
32789 4/18/2018 10:00:00 AM 4/18/2018 10:50:00 AM
32789 4/25/2018 10:00:00 AM 4/25/2018 10:50:00 AM
32789 5/02/2018 10:00:00 AM 5/02/2018 10:50:00 AM
32789 1/12/2018 10:00:00 AM 1/12/2018 10:50:00 AM
32789 1/19/2018 10:00:00 AM 1/19/2018 10:50:00 AM
32789 1/26/2018 10:00:00 AM 1/26/2018 10:50:00 AM
32789 2/02/2018 10:00:00 AM 2/02/2018 10:50:00 AM
32789 2/09/2018 10:00:00 AM 2/09/2018 10:50:00 AM
32789 2/16/2018 10:00:00 AM 2/16/2018 10:50:00 AM
32789 2/23/2018 10:00:00 AM 2/23/2018 10:50:00 AM
32789 3/02/2018 10:00:00 AM 3/02/2018 10:50:00 AM
32789 3/09/2018 10:00:00 AM 3/09/2018 10:50:00 AM
32789 3/16/2018 10:00:00 AM 3/16/2018 10:50:00 AM
32789 3/23/2018 10:00:00 AM 3/23/2018 10:50:00 AM
32789 3/30/2018 10:00:00 AM 3/30/2018 10:50:00 AM
32789 4/06/2018 10:00:00 AM 4/06/2018 10:50:00 AM
32789 4/13/2018 10:00:00 AM 4/13/2018 10:50:00 AM
32789 4/20/2018 10:00:00 AM 4/20/2018 10:50:00 AM
32789 4/27/2018 10:00:00 AM 4/27/2018 10:50:00 AM
32789 5/04/2018 10:00:00 AM 5/04/2018 10:50:00 AM
如果班级在当天开会,则日期列只有指示符。(星期日'U',星期一'M',星期二'T',星期三'W',星期四'R',星期五'F',星期六'S')
开始时间和结束时间为 24 小时格式。
我尝试了几种不同的路径来尝试解决,包括使用 connect by this 但我很难过。
这就是我所掌握的。我可以用它生成第一个日期,但我不知道从哪里开始..
with
data_prep as(
select 'U' dotw_code, 'Sunday' week_day from dual
union all
select 'M', 'Monday' from dual
union all
select 'T', 'Tuesday' from dual
union all
select 'W', 'Wednesday' from dual
union all
select 'R', 'Thursday' from dual
union all
select 'F', 'Friday' from dual
union all
select 'S', 'Saturday' from dual
)
select a.ssrmeet_crn crn
,case b.dotw_code
when 'U'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_begin_time, 'MM/DD/YYYY HH24:MI:SS'),'SUN')
when 'M'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_begin_time, 'MM/DD/YYYY HH24:MI:SS'),'MON')
when 'T'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_begin_time, 'MM/DD/YYYY HH24:MI:SS'),'TUE')
when 'W'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_begin_time, 'MM/DD/YYYY HH24:MI:SS'),'WED')
when 'R'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_begin_time, 'MM/DD/YYYY HH24:MI:SS'),'THU')
when 'F'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_begin_time, 'MM/DD/YYYY HH24:MI:SS'),'FRI')
when 'S'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_begin_time, 'MM/DD/YYYY HH24:MI:SS'),'SAT')
end start_date
,case b.dotw_code
when 'U'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_end_time, 'MM/DD/YYYY HH24:MI:SS'),'SUN')
when 'M'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_end_time, 'MM/DD/YYYY HH24:MI:SS'),'MON')
when 'T'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_end_time, 'MM/DD/YYYY HH24:MI:SS'),'TUE')
when 'W'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_end_time, 'MM/DD/YYYY HH24:MI:SS'),'WED')
when 'R'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_end_time, 'MM/DD/YYYY HH24:MI:SS'),'THU')
when 'F'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_end_time, 'MM/DD/YYYY HH24:MI:SS'),'FRI')
when 'S'
then
next_day(to_date(to_char(ssrmeet_start_date, 'MM/DD/YYYY') || ' ' || ssrmeet_end_time, 'MM/DD/YYYY HH24:MI:SS'),'SAT')
end end_date
from ssrmeet a
left join data_prep b
on a.ssrmeet_sun_day = b.dotw_code
or a.ssrmeet_mon_day = b.dotw_code
or a.ssrmeet_tue_day = b.dotw_code
or a.ssrmeet_wed_day = b.dotw_code
or a.ssrmeet_thu_day = b.dotw_code
or a.ssrmeet_fri_day = b.dotw_code
or a.ssrmeet_sat_day = b.dotw_code
where ssrmeet_term_code = 201730
and ssrmeet_crn = 32789
如果有人可以提供帮助,甚至可以指导我如何在论坛上正确格式化我的问题,我将不胜感激。
如果我理解正确(您希望在开始日期和结束日期之间列出会议应该发生的日期),那么我认为以下查询应该适合您:
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_date)。如果有多于一行,这将为每一行生成分层查询。如果您只希望一次对一行执行此操作,则只需要 connect by 子句中的最后一行(前三行是为每一行创建层次结构所必需的,而不是跨行集。我假设 ssrmeet_term_code, ssrmeet_crn 是唯一的)。
获得此列表后,您可以检查日期标志以查看日期是否为标记日期之一,如果不是,则不要输出它。(注意,由于您没有提供任何示例数据或预期输出,我不得不猜测表格内容和预期输出。)
如果您有机会更改设计,我会在日标志列中使用简单的 Y/N(或 Y/null),而不是每列都有自己的单独标志(即 mon_flag = Y, tue_flag = Y 等,而不是 mon_flag = 'M'、tue_flag = 'T' 等)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句