时间戳

首页 > 解决方案 > 根据另一个表数据创建另一个表和列

sql - 根据另一个表数据创建另一个表和列

问题描述

我有下表：

表格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 的值，表 1 中 Col3 的多少记录具有 11 作为值。

对于 22,33 和其他值也是如此。

输出表2 在此处输入图像描述

标签： sqloracle

解决方案

由于不知道甲骨文版本，我会尽一切可能回答我的问题，

因为 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查询的东西。

推荐阅读