显示记录，即使它不存在 [英] Display record even if it doesn't exist

问题描述

所以我有这张桌子

Col1    Col2    Col3
A       34       X
B       43       L
A       36       L

现在，如果我查询

select * from Table1 where col1 in ('A','B','C')

我希望有类似的东西

Col1    Col2    Col3
A       34       X
B       43       L
A       36       L
C       -        -

有可能吗?

P.S:C行中的-只是为了表明该列为空.

P.S: the - in row C are just to show that the column is empty.

推荐答案

您可以创建嵌套表架构对象类型:

You could create a nested table schema object type:

create type T_List1 as table of varchar2(100);

然后按如下所示构造查询:

And then construct your query as follows:

 select s.column_value            as col1
      , nvl(to_char(t.col2), '-') as col2
      , nvl(col3, '-')            as col3
  from Table1 t
 right join table(T_List1('A', 'B', 'C')) s
    on (t.col1 = s.column_value)

示例:

-- sample of data from your question
with Table1(Col1, Col2, Col3) as(
  select 'A',  34,  'X' from dual union all
  select 'B',  43,  'L' from dual union all
  select 'A',  36,  'L' from dual
)  -- actual query
 select s.column_value            as col1
      , nvl(to_char(t.col2), '-') as col2
      , nvl(col3, '-')            as col3
   from Table1 t
  right join table(T_List1('A', 'B', 'C')) s --< here list your values
     on (t.col1 = s.column_value)            -- as you would using `IN` clause

结果:

COL1  COL2   COL3
------------------------
A     36     L    
A     34     X    
B     43     L    
C     -      -   

SQLFiddle演示

这篇关于显示记录，即使它不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！