Блог

Главная - Вопросы по программированию - Создание другой таблицы и столбцов на основе данных другой таблицы

Создание другой таблицы и столбцов на основе данных другой таблицы

#sql #oracle

#sql #Oracle

Вопрос:

У меня есть следующая таблица :

Таблица 1

 Create  table Table1 (Col1 Varchar2(50) not null, Col2 Varchar2(50), Col3 Varchar2(50));

Insert into Table1 (col1, col2, col3) values 
('RED','aa','11,22,33'),
('Green','bb','33'),
('blue','bb','11,44'), 
('yellow','bb','55,66'), 
('orange','bb','22,33');

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

Colx_11 будет иметь значение, основанное на Table1, сколько записей в Table1 для Col3 имеет значение 11 в качестве значения.

Аналогично для 22,33 и других значений.

Выходная таблица2 введите описание изображения здесь

Комментарии:

1. Требуется sql-запрос для получения выходных данных, показанных в Table2

2. почему это помечено как oracle ? mysql и oracle 2 разные базы данных

3. @KoenLostrie Спасибо, это было по ошибке. Сейчас я это исправил.

4. Может ли столбец Col3 содержать одно и то же число дважды? Например 11,11,22 . Если да, вы считаете 11 дважды или один раз?

5. Нет дубликатов в том же столбце.

Ответ №1:

Поскольку версия oracle неизвестна, я бы поставил свой ответ со всеми возможностями,

Поскольку в oracle нет конкретной предопределенной функции, доступной для простого разделения записей, разделенных запятыми, нам нужно использовать regular expression ее для разделения, а затем подсчитать вхождение.

Использование условной агрегации (работа с любой версией> 8i):

 select sum(case when split_number = 11 then 1 else 0 end) as col_11
      ,sum(case when split_number = 22 then 1 else 0 end) as col_22
      ,sum(case when split_number = 33 then 1 else 0 end) as col_33
      ,sum(case when split_number = 44 then 1 else 0 end) as col_44
      ,sum(case when split_number = 55 then 1 else 0 end) as col_55
      ,sum(case when split_number = 66 then 1 else 0 end) as col_66
  from table1 t1
  join (select distinct col3,regexp_substr(col3,'[^,] ', 1, level) split_number
         from table1 t2
        connect by regexp_substr(col3, '[^,] ', 1, level) is not null) t2
    on t2.col3 = t1.col3;

Использование PIVOT и JOIN (работа с любой версией> = 11g):

 select *
  from
(
select split_number
  from table1 t1
  join (select distinct col3,regexp_substr(col3,'[^,] ', 1, level) split_number
         from table1 t2
        connect by regexp_substr(col3, '[^,] ', 1, level) is not null) t2
    on t2.col3 = t1.col3
)
pivot
(
 count(*)
 for split_number in ( 11 as col_11
                     , 22 as col_22
                     , 33 as col_33
                     , 44 as col_44
                     , 55 as col_55
                     , 66 as col_66)
);

Использование PIVOT и LATERAL (работа с любой версией> = 12c):

 select *
  from
(
select split_number
  from table1 t1,
  lateral (select regexp_substr(col3,'[^,] ', 1, level) split_number
             from dual
           connect by regexp_substr(col3, '[^,] ', 1, level) is not null) t2
)
pivot
(
 count(*)
 for split_number in ( 11 as col_11
                     , 22 as col_22
                     , 33 as col_33
                     , 44 as col_44
                     , 55 as col_55
                     , 66 as col_66)
);

Я все же хотел бы упомянуть здесь, что недостатком приведенных выше запросов является явное упоминание столбцов типа col_11 и col_22 … и т.д., и для этой динамики нам понадобится динамический SQL или что-нибудь с XML запросами.