Блог

Главная - Вопросы по программированию - Как обрабатывать дополнительные строки, созданные при разделении значений столбца, разделенных запятыми, на строки в Oracle?

Как обрабатывать дополнительные строки, созданные при разделении значений столбца, разделенных запятыми, на строки в Oracle?

#sql #oracle

Вопрос:

Я столкнулся с проблемой при разделении значения поля OTA на строки, здесь мне нужны две строки со значением ANR в поле OTA, в то время как я получаю 3 строки. Выход дает 4 строки, хотя я должен получить только 3 строки, 2 для дизельной тяги, где OTA-ANR, ANR и 1 строка для электрической тяги, где OTA-LL, я не понимаю, почему он дает одну дополнительную строку для ANR, пожалуйста, руководство.

Используемый запрос является :

 SELECT TRAIN_ID,TRAIN_NUMBER,TRAIN_START_DATE,TRAIN_TYPE,TRACTION,TRAIN_SRC,TRAIN_DSTN,
     REGEXP_SUBSTR(OTA,'[^,] ', 1, level)AS OTA 
     FROM 
    (
    SELECT TRAIN_ID,TRAIN_NUMBER,TRAIN_START_DATE,TRAIN_TYPE,TRACTION,TRAIN_SRC,TRAIN_DSTN,
       (CASE WHEN (MULTIPLE_UNIT='1' AND OTA='ANR') THEN 'ANR,ANR'
         WHEN (MULTIPLE_UNIT='1' AND (IS_BANKING=1 OR IS_AEB=1) AND OTA='LL') THEN 'LL,AR,AR'
         WHEN (MULTIPLE_UNIT='1' AND OTA='LL') THEN 'LL,AR'
         WHEN (MULTIPLE_UNIT='0' AND OTA='ANR')THEN 'ANR'
         WHEN (MULTIPLE_UNIT='0' AND (IS_BANKING=1 OR IS_AEB=1) AND OTA='LL') THEN 'LL,AR'
         WHEN (MULTIPLE_UNIT='0' AND OTA='LL') THEN 'LL'         
         END)AS OTA
          FROM SCHEDULED_RUN 
          WHERE TRAIN_NUMBER='12543' AND TRAIN_START_DATE='06-May-2021'
          AND SRC_SERIAL_NUMBER=1
        )CONNECT BY LEVEL<= regexp_count(OTA, ',') 1 and prior TRAIN_ID=TRAIN_ID and prior sys_guid() IS NOT NULL

Вывод внутреннего запроса выглядит следующим образом :

введите описание изображения здесь

Вывод внешнего запроса выглядит следующим образом :

введите описание изображения здесь

Ответ №1:

Дубликаты, да. Потому что ты сделал это частично неправильно. Должно было быть

 SQL> with tin (train_id, train_number, train_start_date,
  2    train_type, traction, train_src, train_dstn, ota) as
  3    (select '1111X', 12543, date '2021-05-06', 'RAJ', 'D', 'JHS', 'PRYJ', 'ANR,ANR' from dual union all
  4     select '1111X', 12543, date '2021-05-06', 'RAJ', 'E', 'JHS', 'PRYJ', 'LL'      from dual
  5    )
  6  select train_id, train_number, train_start_date,
  7    train_type, traction, train_src, train_dstn,
  8    --
  9    regexp_substr(ota, '[^,] ', 1, column_value) ota
 10  from tin
 11    cross join table(cast(multiset(select level from dual
 12                                   connect by level <= regexp_count(ota, ',')   1
 13                                  ) as sys.odcinumberlist));

TRAIN TRAIN_NUMBER TRAIN_ST TRA T TRA TRAI OTA
----- ------------ -------- --- - --- ---- ----------------------------
1111X        12543 06.05.21 RAJ D JHS PRYJ ANR
1111X        12543 06.05.21 RAJ D JHS PRYJ ANR
1111X        12543 06.05.21 RAJ E JHS PRYJ LL

SQL>

где

  • tin CTE-это результат вашего «внутреннего запроса».
  • обратите внимание, что я использовал перекрестное соединение …
  • … а также column_value в regexp_substr вместо level

Ответ №2:

Вы получаете слишком много строк, потому что у вас дважды один и тот же идентификатор поезда во внутреннем запросе, и вы connect-by этого не допускаете. Поскольку отличительной особенностью, по-видимому, является флаг тяги, вы можете добавить его к условиям:

 CONNECT BY LEVEL<= regexp_count(OTA, ',') 1
and prior TRAIN_ID=TRAIN_ID
and prior TRACTION=TRACTION
and prior sys_guid() IS NOT NULL

Подход @Littlefoot, конечно, тоже работает.

Но создание строки с разделителями только для того, чтобы немедленно разделить ее снова, кажется немного болезненным. Возможно, вы захотите сначала просто создать нужное количество строк с помощью UNION и логики в каждой ветви; что-то вроде (если я правильно следовал существующему выражению case):

 WITH CTE AS (
  SELECT *
  FROM SCHEDULED_RUN 
  WHERE TRAIN_NUMBER = 12543 AND TRAIN_START_DATE = DATE '2021-05-06'
  AND SRC_SERIAL_NUMBER = 1
)
-- always at least one row
SELECT TRAIN_ID, TRAIN_NUMBER, TRAIN_START_DATE, TRAIN_TYPE, TRACTION, TRAIN_SRC, TRAIN_DSTN, OTA
FROM CTE
UNION ALL
-- if multiple unit = 1 and OTA = ANR then repeat same row
SELECT TRAIN_ID, TRAIN_NUMBER, TRAIN_START_DATE, TRAIN_TYPE, TRACTION, TRAIN_SRC, TRAIN_DSTN, OTA
FROM CTE
WHERE OTA ='ANR' AND MULTIPLE_UNIT = 1
UNION ALL
-- LL with banking/AEB gets an AR row...
SELECT TRAIN_ID, TRAIN_NUMBER, TRAIN_START_DATE, TRAIN_TYPE, TRACTION, TRAIN_SRC, TRAIN_DSTN, 'AR' AS OTA
FROM CTE
WHERE OTA = 'LL' AND (IS_BANKING = 1 OR IS_AEB = 1)
UNION ALL
-- ... and a second AR row if multiple unit = 1
SELECT TRAIN_ID, TRAIN_NUMBER, TRAIN_START_DATE, TRAIN_TYPE, TRACTION, TRAIN_SRC, TRAIN_DSTN, 'AR' AS OTA
FROM CTE
WHERE OTA = 'LL' AND (IS_BANKING = 1 OR IS_AEB = 1) AND MULTIPLE_UNIT = 1

Я также изменил сравнение дат, чтобы использовать реальную дату, а не строку (которая зависит от неявного преобразования), а также некоторые другие сравнения, чтобы использовать числа вместо строк.

db<>скрипка

Ответ №3:

Это можно решить, не используя строки с разделителями (следовательно, вам не нужно разделять строки), а вместо этого используя тип данных коллекции (или VARRAY такой как SYS.ODCIVARCHAR2LIST ) :

 SELECT t.TRAIN_ID,
       t.TRAIN_NUMBER,
       t.TRAIN_START_DATE,
       t.TRAIN_TYPE,
       t.TRACTION,
       t.TRAIN_SRC,
       t.TRAIN_DSTN,
       o.COLUMN_VALUE AS OTA 
FROM   (
  SELECT TRAIN_ID,
         TRAIN_NUMBER,
         TRAIN_START_DATE,
         TRAIN_TYPE,
         TRACTION,
         TRAIN_SRC,
         TRAIN_DSTN,
         ( CASE
           WHEN (MULTIPLE_UNIT='1' AND OTA='ANR')
           THEN SYS.ODCIVARCHAR2LIST('ANR','ANR')
           WHEN (MULTIPLE_UNIT='1' AND (IS_BANKING=1 OR IS_AEB=1) AND OTA='LL')
           THEN SYS.ODCIVARCHAR2LIST('LL','AR','AR')
           WHEN (MULTIPLE_UNIT='1' AND OTA='LL')
           THEN SYS.ODCIVARCHAR2LIST('LL','AR')
           WHEN (MULTIPLE_UNIT='0' AND OTA='ANR')
           THEN SYS.ODCIVARCHAR2LIST('ANR')
           WHEN (MULTIPLE_UNIT='0' AND (IS_BANKING=1 OR IS_AEB=1) AND OTA='LL')
           THEN SYS.ODCIVARCHAR2LIST('LL','AR')
           WHEN (MULTIPLE_UNIT='0' AND OTA='LL')
           THEN SYS.ODCIVARCHAR2LIST('LL')
           END
         ) AS OTAs
  FROM   SCHEDULED_RUN
  WHERE  TRAIN_NUMBER='12543'
  AND    TRAIN_START_DATE=DATE '2021-05-06'
  AND    SRC_SERIAL_NUMBER=1
) t
CROSS JOIN TABLE(t.OTAs) o

(С Oracle 12 вы можете перейти CROSS JOIN на CROSS APPLY или, при необходимости, OUTER APPLY .)

Который, для выборочных данных:

 CREATE TABLE SCHEDULED_RUN (
  src_serial_number, train_id, train_number, train_start_date, train_type, traction, train_src, train_dstn, ota, multiple_unit, is_banking, is_aeb
) as
  select 1, '1111X', 12543, date '2021-05-06', 'RAJ', 'A', 'JHS', 'PRYJ', 'ANR', 1, 0, 0 from dual union all
  select 1, '1111X', 12543, date '2021-05-06', 'RAJ', 'B', 'JHS', 'PRYJ', 'LL',  1, 1, 0 from dual union all
  select 1, '1111X', 12543, date '2021-05-06', 'RAJ', 'C', 'JHS', 'PRYJ', 'LL',  1, 0, 0 from dual union all
  select 1, '1111X', 12543, date '2021-05-06', 'RAJ', 'D', 'JHS', 'PRYJ', 'ANR', 0, 0, 0 from dual union all
  select 1, '1111X', 12543, date '2021-05-06', 'RAJ', 'E', 'JHS', 'PRYJ', 'LL',  0, 0, 1 from dual union all
  select 1, '1111X', 12543, date '2021-05-06', 'RAJ', 'F', 'JHS', 'PRYJ', 'LL',  0, 0, 0 from dual;

Выходы:

TRAIN_ID НОМЕР ПОЕЗДА TRAIN_START_DATE ТИП ПОЕЗДА тяга TRAIN_SRC TRAIN_DSTN ОТА
1111X 12543 06-21 МАЯ РАДЖ A JHS ПРЙЖ ANR
1111X 12543 06-21 МАЯ РАДЖ A JHS ПРЙЖ ANR
1111X 12543 06-21 МАЯ РАДЖ B JHS ПРЙЖ лл
1111X 12543 06-21 МАЯ РАДЖ B JHS ПРЙЖ AR
1111X 12543 06-21 МАЯ РАДЖ B JHS ПРЙЖ AR
1111X 12543 06-21 МАЯ РАДЖ C JHS ПРЙЖ лл
1111X 12543 06-21 МАЯ РАДЖ C JHS ПРЙЖ AR
1111X 12543 06-21 МАЯ РАДЖ D JHS ПРЙЖ ANR
1111X 12543 06-21 МАЯ РАДЖ E JHS ПРЙЖ лл
1111X 12543 06-21 МАЯ РАДЖ E JHS ПРЙЖ AR
1111X 12543 06-21 МАЯ РАДЖ F JHS ПРЙЖ лл

бд<>скрипка <>здесь

Метки: , , ,