Oracle 10g - 动态展平关系数据

Question

我使用的是Oracle 10g。我有以下关系结构，我认为我需要将其展平，以便在客户端网格视图显示正确的列数。

下面的表 A 可以配置为使用启用字段启用或禁用其任何一条记录。

TableB通过字段fk存储与TableA相关的计算值。对于 docid 1，计算了“nm1”和“nmn4”的值，但没有计算“nm2”的值。

我的问题是，对于 TableA 的特定配置，我需要返回一个记录集，其中包含 TableA 中启用的记录的完整补充，无论 TableB 中的 docid 是否具有计算值。我希望动态创建的输出如下所示。

有什么想法吗？

TableA
id     nm     enabled
1     'nm1'   1
2     'nm2'   1
3     'nm3'   0
4     'nm4'   1


TableB
id     fk(A.id)     docid     value
1      1            1         .8
2      4            1         .6
3      1            2         .3
4      2            2         .4
5      4            2         .7
6      2            3         .6
7      4            3         .8

Output as records
1     'nm1'     .8       'nm2'     null     'nm4'     .6
2     'nm1'     .3       'nm2'     .4       'nm4'     .7
3     'nm1'     null     'nm2'     .6       'nm4'     .8

Answer 1

这看起来像 将查询转给我。您可以通过将表 B 与表 A 连接，然后限制启用（例如 select B.* from B, A where B.A_id = A.id and A.enabled = 1）来进行过滤。然后你可以旋转它。

Answer 2

我确信有更好的方法可以做到这一点，但这就是我想出的方法。另外，您发布的问题似乎有点含糊，所以我不完全确定我是否正确回答了问题。

首先，您需要获得所有可能结果的稀疏表，然后再次连接以获取值。

其输出为单列数据。如果不使用动态 SQL 或其他东西创建查询，实际上不可能为每个查询提供自定义的列数。

sys_connect_by_path 用于将多行数据连接成一行。

with table_a as (
  select 1 as id, 'nm1' as nm, 1 as enabled from dual union all
  select 2 as id, 'nm2' as nm, 1 as enabled from dual union all
  select 3 as id, 'nm3' as nm, 0 as enabled from dual union all
  select 4 as id, 'nm4' as nm, 1 as enabled from dual
),
table_b as (
  select 1 as id, 1 as a_id, 1 as docid, 0.8 as value from dual union all
  select 2 as id, 4 as a_id, 1 as docid, 0.6 as value from dual union all
  select 3 as id, 1 as a_id, 2 as docid, 0.3 as value from dual union all
  select 4 as id, 2 as a_id, 2 as docid, 0.4 as value from dual union all
  select 5 as id, 4 as a_id, 2 as docid, 0.7 as value from dual union all
  select 6 as id, 2 as a_id, 3 as docid, 0.6 as value from dual union all
  select 7 as id, 4 as a_id, 3 as docid, 0.8 as value from dual 
),
cartesian_prod as (
  select b.docid, a.id, a.nm
  from 
    table_a a
    cross join (select distinct docid from table_b) b
  where a.enabled = 1
)
select 
  docid, 
  ltrim(max(sys_connect_by_path(nm || ' ' || value, ', ')), ', ') as value
from (
  select 
    c.docid, 
    c.nm, 
    nvl(to_char(b.value), 'null') as value, 
    row_number() over (partition by c.docid order by c.id) as rown
  from 
    cartesian_prod c 
    left outer join table_b b on (b.docid = c.docid and c.id = b.a_id)
)
start with rown = 1
connect by docid = prior docid and rown = prior rown + 1
group by docid