代码之家 › 专栏 › 技术社区 › Nicholas Riley

外部连接条件?

postgresql sql

Nicholas Riley · 技术社区 · 15 年前

我试图查找两个元数据(卷和问题),它们可能有卷、问题或两者都有。元数据存储在具有项ID、键(元数据字段ID)和值的表中。

这确实有效,但似乎过于复杂和重复:

select volume.text_value as volume_value, issue.text_value as issue_value
    from metadatavalue item
    left outer join (select item_id, text_value from metadatavalue
                     where metadata_field_id = 90) volume
                    on item.item_id = volume.item_id
    left outer join (select item_id, text_value from metadatavalue
                     where metadata_field_id = 91) issue
                    on item.item_id = issue.item_id
    where item.metadata_field_id in (90, 91)

谢谢

5 回复 | 直到 15 年前

John MacIntyre 15 年前

试试这个

select  volume.text_value as volume_value, 
        issue.text_value as issue_value    
from    metadatavalue item    
        left outer join metadatavalue volume                    
            on item.item_id = volume.item_id    
        left outer join metadatavalue issue                    
            on item.item_id = issue.item_id    
where   volume.metadata_field_id = 90
and     issue.metadata_field_id = 91

Nicholas Riley 15 年前

PostgreSQL支持完全外部联接,可以简化查询:

  select v.text_value as volume_value, i.text_value as issue_value
      from ( select item_id, text_value
               from metadatavalue
              where metadata_field_id = 90) v
           full join
           ( select item_id, text_value
               from metadatavalue
              where metadata_field_id = 91) i
           using (item_id)

Quassnoi 15 年前

SELECT  DISTINCT ON (item_id)
        item_id,
        CASE metadata_field_id
        WHEN 90 THEN
                text_value
        ELSE    (
                SELECT  text_value
                FROM    metadatavalue m
                WHERE   m.metadata_field_id = 90
                        AND m.item_id = i.item_id
                )
        END AS volume,
        CASE metadata_field_id
        WHEN 91 THEN
                text_value
        ELSE    (
                SELECT  text_value
                FROM    metadatavalue m
                WHERE   m.metadata_field_id = 91
                        AND m.item_id = i.item_id
                )
        END AS issue
FROM    metadatavalue
WHERE   metadata_field_id IN (90, 91)
ORDER BY
        item_id

索引 (item_id, metadata_field) 将改进此查询。

如果数量很少,这将更有效 items metadata 属于 90 91 项目 .

项目 有这些吗

SELECT  *
FROM    (
        SELECT  item_id,
                (
                SELECT  text_value
                FROM    metadatavalue m
                WHERE   m.metadata_field_id = 90
                        AND m.item_id = i.item_id
                ) volume,
                (
                SELECT  text_value
                FROM    metadatavalue m
                WHERE   m.metadata_field_id = 91
                        AND m.item_id = i.item_id
                ) issue
        FROM    items
        ) q
WHERE   issue IS NOT NULL OR volume IS NOT NULL

tom 15 年前

使用case语句将值分离到存储桶中,max函数将允许文本值数据浮到顶部。

select
   item_id
  ,max(case metadata_field_id when 90 then text_value else null end) as volume_value
  ,max(case metadata_field_id when 91 then text_value else null end) as issue_value
from
  metadatavalue
group by
   item_id

select
   item_id
  ,max(case metadata_field_id when 90 then text_value else null end) as volume_value
  ,max(case metadata_field_id when 91 then text_value else null end) as issue_value
from
  (
   select 1 as item_id, 90 as metadata_field_id, '90-I am here' as text_value
   union
   select 1 as item_id, 91 as metadata_field_id, '91-Me too' as text_value
   union
   select 2 as item_id, 90 as metadata_field_id, null as text_value
   union
   select 2 as item_id, 91 as metadata_field_id, '91-funky' as text_value
   union
   select 3 as item_id, 90 as metadata_field_id, '90-fresh' as text_value
  ) metadatavalue
group by
   item_id

item_id  volume_value  issue_value
1        90-I am here  91-Me too
2        NULL          91-funky
3        90-fresh      NULL

注意:我使用SQL Server建模&测试,然后更改语法以匹配Postgres。

araqnid 15 年前

您可以将谓词从内部表中取出,放入联接条件:

select volume.text_value as volume_value, issue.text_value as issue_value
from metadatavalue item
left outer join metadatavalue volume
                on volume.metadata_field_id = 90 and volume.item_id = item.item_id
left outer join metadatavalue issue
                on issue.metadata_field_id = 91 and issue.item_id = item.item_id
where item.metadata_field_id in (90, 91)

虽然我不确定我是否理解你的模式,但在我看来 item 应参考不同于的表 volume 和 issue

select item.*, volume.text_value as volume_value, issue.text_value as issue_value
from item
left outer join metadatavalue volume
                on volume.metadata_field_id = 90 and volume.item_id = item.item_id
left outer join metadatavalue issue
                on issue.metadata_field_id = 91 and issue.item_id = item.item_id

这也使得情况变得相当奇怪。您可以将上面的查询放入一个视图(“item\u volume\u issue”),看起来volume和issue只是item\u volume\u issue中的附加列,而不是单独的表(这对我来说更有意义)。