SQL联接/联合 [英] SQL Join / Union

问题描述

我有两个要合并为一个输出的语句.

I have two statements that I want to merge into one output.

声明一:

select name from auxiliary_variable_inquiry
where inquiry_idbr_code = '063' 

返回以下名称列表:

Name
------------
Affiliates
NetBookValue
Parents
Worldbase

声明二:

select name, value from auxiliary_variable_value
where inquiry_idbr_code = '063'
and ru_ref = 20120000008
and period = 200912

返回以下内容:

Name        Value
-------------------
Affiliates      112
NetBookValue    225.700

我想要这样的输出:

Name         Value
-------------------
Affiliates    112 
NetBookValue  225.700
Parents       0
Worldbase     0

因此，基本上，如果第二个查询仅返回2个名称和值，我仍然想显示第一个查询的完整名称集，不包含任何值.如果两个查询都返回了所有四个值，则将显示所有四个值.

So basically, if the second query only returns 2 names and values, I'd still like to display the complete set of names from the first query, with no values. If all four values were returned by both queries, then all four would be displayed.

抱歉，我必须使用Ingres SQL进行添加，因此无法使用ISNULL函数.

Sorry I must add, im using Ingres SQL so im unable to use the ISNULL function.

推荐答案

我建议使用LEFT OUTER JOIN语法进行自我联接.将第二个查询中的额外"条件包括在JOIN条件中，而第一个条件保留在WHERE中，如下所示:

I'd recommend a self-JOIN using the LEFT OUTER JOIN syntax. Include your 'extra' conditions from the second query in the JOIN condition, while the first conditions stay in the WHERE, like this:

    select a.name, CASE WHEN b.Value IS NULL THEN 0 ELSE b.Value END AS Value
    from 
        auxiliary_variable_inquiry a
         LEFT JOIN 
        auxiliary_variable_inquiry b ON 
            a.name = b.name and -- replace this with your real ID-based JOIN 
            a.inquiry_idbr_code = b.inquiry_idbr_code AND
            b.ru_ref = 20120000008 AND
            b.period = 200912
    where a.inquiry_idbr_code = '063' 

这篇关于SQL联接/联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！