SQL按行刷新搜索

Question

我试图使用选择脚本从数据库中的表中提取工作日列。并将"MWF"划分为"Monday"，"Wednesday"one_answers"Friday"。每天需要在单独的行中显示。

这里是:

SELECT
    REGEXP_SUBSTR(MEETING_DAY, '[^,]+', 1, LEVEL, 'i'), FROM_TIME_HHIIAP  as DAY 
FROM
    (SELECT
        CASE WHEN (DAY_MEET.MEETING_DAYS = 'F')THEN ('Friday')
             WHEN (DAY_MEET.MEETING_DAYS = 'FS')THEN ('Friday,Saturday')
             WHEN (DAY_MEET.MEETING_DAYS = 'M')THEN ('Monday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MF')THEN ('Monday,Friday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MT')THEN ('Monday,Tuesday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MTH')THEN ('Monday,Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MTTH')THEN ('Monday,Tuesday,Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MTW')THEN ('Monday,Tuesday,Wednesday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MTWTH')THEN ('Monday,Tuesday,Wednesday,Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MTWTHF')THEN ('Monday,Tuesday,Wednesday,Thursday,Friday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MW')THEN ('Monday,Wednesday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MWF')THEN ('Monday,Wednesday,Friday')
             WHEN (DAY_MEET.MEETING_DAYS = 'MWTH')THEN ('Monday,Wednesday,Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'S')THEN ('Saturday')
             WHEN (DAY_MEET.MEETING_DAYS = 'T')THEN ('Tuesday')
             WHEN (DAY_MEET.MEETING_DAYS = 'TH')THEN ('Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'THF')THEN ('Thursday,Friday')
             WHEN (DAY_MEET.MEETING_DAYS = 'TTH')THEN ('Tuesday,Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'TTHF') THEN  ('Tuesday,Thursday,Friday')
             WHEN (DAY_MEET.MEETING_DAYS = 'TW')THEN ('Tuesday,Wednesday')
             WHEN (DAY_MEET.MEETING_DAYS = 'TWTH')THEN ('Tuesday,Wednesday,Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'W')THEN ('Wednesday')
             WHEN (DAY_MEET.MEETING_DAYS = 'WF')THEN ('Wednesday,Friday')
             WHEN (DAY_MEET.MEETING_DAYS = 'WTH')THEN ('Wednesday,Thursday')
             WHEN (DAY_MEET.MEETING_DAYS = 'WTHF')THEN ('Wednesday,Thursday,Friday')
             ELSE ('Unknown')
         END MEETING_DAY, 
         DAY_MEET.*
     FROM 
         UTDBA.IS_167_DAY12_MEETING_INFO DAY_MEET)
CONNECT BY LEVEL <=  
           LENGTH(MEETING_DAY) - LENGTH(REGEXP_REPLACE(MEETING_DAY, ',', ''))+1; 

但是似乎LEVEL停留在第一行之后的最后一个字符，所以结果是错误的。有人知道如何刷新级别回到每一行的第一个单词吗?

Answer 1

嗯。您可以使用join:

来完成此操作。

select mi.*, d.name
from (select mi.*,
             replace(mi.meeting_days, 'TH', 'X') as meeting_days_x -- do something about Thursday
      from UTDBA.IS_167_DAY12_MEETING_INFO mi
     ) mi join
     (select 'M' as abbrev, 'Monday' as name from dual union all
      select 'T' as abbrev, 'Tuesday' as name from dual union all
      select 'W' as abbrev, 'Wednesday' as name from dual union all
      select 'X' as abbrev, 'Thursday' as name from dual union all
      select 'F' as abbrev, 'Friday' as name from dual union all
      select 'S' as abbrev, 'Saturday' as name from dual
     ) d
     on mi.meeting_days_x like '%' || d.abbrev || '%';