sql - Oracle 查询 - 结果中的更多记录与表中一样

Question

我想选择带有附加行（030）的数据表单表，它应该包含其他列的所有可能组合

例如：

 Select * from table where row in ('010,'020','030')

comp       period       row       column        value
1          30.06.2017   010       020           500
1          30.06.2017   020       020           300
2          30.06.2017   010       020           400
3          30.06.2017   020       020           100

结果应该是这样的

comp       period       row       column        value
1          30.06.2017   010       020           500
1          30.06.2017   020       020           300
2          30.06.2017   010       020           400
2          30.06.2017   020       020           (null)
3          30.06.2017   020       020           100
3          30.06.2017   010       020           (null)
1          30.06.2017   030       020           (null)
2          30.06.2017   030       020           (null)
3          30.06.2017   030       020           (null)

标签： sqloracleselect

Answer 1

使用 across join生成行，然后left join引入当前结果。我想你想要：

Select cpr.*, r.row, t.value
from (select distinct comp, period, column from t) cpr cross join
     (select '010' as row from dual union all
      select '020' as row from dual union all
      select '030' as row from dual 
     ) r left join
     t
     on t.comp = cpr.comp and t.period = cpr.period and t.column = cpr.column and t.row = cpr.row;