问SQL Search按行刷新
EN

Stack Overflow用户

提问于 2016-06-25 03:16:23

回答 1查看 42关注 0票数 0

我正在尝试使用select脚本从数据库中的表中提取工作日列。将"MWF“分为”星期一“、”星期三“和”星期五“。每一天都需要显示在单独的行中。

我这里有的是：

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;

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

sql

oracle

回答 1

Stack Overflow用户

发布于 2016-06-25 03:21:52

嗯。您可以使用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 || '%';

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/38020611

复制

相似问题

问SQL Search按行刷新
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Search按行刷新EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Search按行刷新
EN