主页 / dba / 问题 / 317083
Accepted
Nan0
Asked: 2022-09-19 23:59:38 +0800 CST

来自双矩阵的 0 和 1

  • 772

我需要用 0 和 1 组合制作一个矩阵：

SELECT 1 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 1 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,1 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,0 AS F3,1 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 1 AS F5 FROM DUAL

您是否有一些更棘手或更优雅的代码来实现这一目标？

我在 Oracle 19c

谢谢

oracle functions

2 个回答

  1. 您可以让数据库为您完成，而不是自己创建组合：

    with d(x) as ( select 0 from dual union all select 1 from dual )
select d1.x as F1
     , d2.x as F2
     , d3.x as F3
     , d4.x as F4
     , d5.x as F5 
from d d1
cross join d d2
cross join d d3
cross join d d4
cross join d d5;

    编辑：误读你的问题

    with d(x) as ( select 0 from dual union all select 1 from dual )
select case when rownum = 1 then 1 else 0 end as F1
     , case when rownum = 2 then 1 else 0 end as F2
     , case when rownum = 3 then 1 else 0 end as F3
     , case when rownum = 4 then 1 else 0 end as F4
     , case when rownum = 5 then 1 else 0 end as F5
from d d1
cross join d d2
cross join d d3
order by rownum  
fetch first 5 rows only
;

    除了交叉连接有 2 行的表外，还可以按多维数据集使用分组：

    with d(x) as ( select 0 from dual group by cube(1,1,1) )
select case when rownum = 1 then 1 else 0 end as F1
     , case when rownum = 2 then 1 else 0 end as F2
     , case when rownum = 3 then 1 else 0 end as F3
     , case when rownum = 4 then 1 else 0 end as F4
     , case when rownum = 5 then 1 else 0 end as F5
from d d1
order by rownum  
fetch first 5 rows only
;
    • 3
  2. Best Answer

    另外三种方法，一种使用函数SYS.ODCINUMBERLIST()并TABLE()生成 5 行表，第二种使用CONNECT BY（Oracle 用于递归查询的旧方法），第三种滥用GROUPING SETS.

    请注意，TABLE()在最近的版本中不需要。在第一个查询中，我们可以只使用：

    from SYS.ODCINUMBERLIST(1, 2, 3, 4, 5) ;

    在dbfiddle.uk中测试：

    select * from V$VERSION;
    横幅 BANNER_FULL BANNER_LEGACY CON_ID
    Oracle Database 21c Express Edition 发行版 21.0.0.0.0 - 生产 Oracle Database 21c Express Edition 版本 21.0.0.0.0 - 生产
    版本 21.3.0.0.0    		 Oracle Database 21c Express Edition 发行版 21.0.0.0.0 - 生产 0 
    select  
  case when column_value  = 1 then 1 else 0 end as f1,  
  case when column_value  = 2 then 1 else 0 end as f2,
  case when column_value  = 3 then 1 else 0 end as f3,
  case when column_value  = 4 then 1 else 0 end as f4,
  case when column_value  = 5 then 1 else 0 end as f5
from table(
  SYS.ODCINUMBERLIST(1, 2, 3, 4, 5)
) ;
    F1 F2 F3 F4 F5
    1 0 0 0 0
    0 1 0 0 0
    0 0 1 0 0
    0 0 0 1 0
    0 0 0 0 1 
    select case when rownum = 1 then 1 else 0 end as F1
     , case when rownum = 2 then 1 else 0 end as F2
     , case when rownum = 3 then 1 else 0 end as F3
     , case when rownum = 4 then 1 else 0 end as F4
     , case when rownum = 5 then 1 else 0 end as F5
from  dual
  connect by level <= 5 ;
    F1 F2 F3 F4 F5
    1 0 0 0 0
    0 1 0 0 0
    0 0 1 0 0
    0 0 0 1 0
    0 0 0 0 1 
    select case when rownum = 1 then 1 else 0 end as F1
     , case when rownum = 2 then 1 else 0 end as F2
     , case when rownum = 3 then 1 else 0 end as F3
     , case when rownum = 4 then 1 else 0 end as F4
     , case when rownum = 5 then 1 else 0 end as F5
from 
  ( select count(*) from dual
    group by grouping sets
      ((), (), (), (), ())  -- 5 groupings
  ) t ;
    F1 F2 F3 F4 F5
    1 0 0 0 0
    0 1 0 0 0
    0 0 1 0 0
    0 0 0 1 0
    0 0 0 0 1

    小提琴

    • 3

相关问题