如何将两个具有公共列(A、B)和(A、C)的查询转换为一个(A、B、C)?

我想是吧 animals.idanimal 是主键。如果是这样,可以使用左外部联接和 count 在要切断的目标列上 NULLs .

select producers.name prod,
       count(animalsbought.idanimal) animalsbought,
       count(animalsexploration.idanimal) animalsexploration
from producers
  join animals on  animals.owner = producers.nif
  left join animalsbought on animalsbought.idanimal = animals.idanimal
  left join animalsexploration on animalsexploration.idanimal = animals.idanimal
group by producers.name;

首先假设这两个查询只是表。你可以这样做:

select a.producer, a.firstquerycolumn, b.secondquerycolumn
from table1 a
join table2 b on b.producer = a.producer

可以用查询(称为内嵌视图)替换每个表:

select a.Prod, a.AnimalsBought, b.AnimalsExploration
from
( select Producers.name Prod, count(Animals.idanimal) AnimalsBought
  from AnimalsBought, Animals, Producers
  where (AnimalsBought.idanimal = Animals.idanimal) 
  and (Animals.owner = Producers.nif) 
  group by Producers.name
) a
join
( select Producers.name Prod, count(Animals.idanimal) AnimalsExploration
  from AnimalsExploration, Animals, Producers
  where (AnimalsExploration.idanimal = Animals.idanimal) 
  and (Animals.owner = Producers.nif)
  group by Producers.name
) b
on a.Prod = b.Prod;

如果一个查询可能为另一个查询不返回数据的生产者返回数据,则可能需要将我的“join”更改为“full outer join”。我还倾向于按如下方式重新构造查询,对外部联接到2个子查询的生产者进行主查询(除去生产者):

select Producers.name Prod, a.AnimalsBought, b.AnimalsExploration
from Producers
left outer join ( select Animals.owner, count(AnimalsBought.idanimal) AnimalsBought
                    from AnimalsBought, Animals
                   where AnimalsBought.idanimal = Animals.idanimal
                   group by Animals.owner
                ) a
           on a.owner = Producers.nif
left outer join ( select Animals.owner, count(Animals.idanimal) AnimalsExploration
                    from AnimalsExploration, Animals
                   where AnimalsExploration.idanimal = Animals.idanimal
                   group by Animals.owner
                ) b
           on b.owner = Producers.nif;

(正是这种类型的查询测试了下面的性能)。

我对Oracle中的标量子查询和内联视图(由Performancedba请求)的相对性能的说明现在在这里离线,而不是用OP可能不感兴趣的信息来膨胀此答案: Notes on Performance

select

tab1.producer
tab1.cola
tab2.colb

from
     (query a) tab1
inner join
     (query b) tab2
on
      tab1.producer = tab2.producer

如果每个查询中不存在每个生产者,您可能希望将联接更改为完全外部联接。

2010年11月28日修订

还是我做错了什么?

对。你知道两个简单的集合,你专注于从这两个集合中产生第三个集合。

1. 对问题采用关系方法(关系模型;codd&date)意味着将其视为集合。两个原始集都是数据库中数据的投影。因此,不需要将结果组合到第三个集合中,只需要考虑一个新的集合,一个直接从数据库中的数据进行的新投影。

2. 其次,这样做之后,直接使用SQL语言获得结果是很容易的。如果理解了(1),所需的编程就是一个简单的逻辑问题。当然,iso/iec/ansi sql标准允许各种代码构造,并且有许多方法可以获得相同的结果;我所说的是程序逻辑;简单性;效率;而不是不。

   这将在任何符合ISO/IEC/ANSI SQL的平台上执行得更快。

   • 众所周知,Oracle是不兼容的,或者扩展了很多标准的定义,为了看起来是兼容的,所以我接受这段代码在Oracle上的执行速度可能不会更快。具体来说,Oracle人员似乎知道标量子查询(这里的逻辑和简单性要求)执行得不好,而是使用其他构造。
   • 任何此类替代结构都背离(1)和(2)。(例如,托尼安德鲁斯的回答从字面上接受了欧普的问题,使用两个已知集合;将它们连接起来;将它们输入第三个集合;然后从中提取3列。)

尝试这种方法,它是一种面向集的方法,用于操作关系数据库,直接从数据(1)生成新的单集,而不考虑两个已知集。然后,它以直接、简单、逻辑的方式使用SQL语言(2)来获得这一需求。是的,这正好是两个标量子查询(返回单个值):

SELECT  Producers.name Prod, 
        (SELECT COUNT(Animals.idanimal)
            FROM Animals,
                 AnimalsBought
            WHERE p.nif = Animals.owner
            AND   Animals.idanimal = AnimalsBought.idanimal
            ) AnimalsBought,
        (SELECT COUNT(Animals.idanimal)
            FROM Animals,
                 AnimalsExploration
            WHERE p.nif = Animals.owner
            AND   Animals.idanimal = AnimalsExploration.idanimal
            ) AnimalsExploration
    FROM Producers p

比较2010年11月29日

本节仅适用于对托尼和我参与的测试感兴趣的人及其结论。

托尼报告说Oracle在2秒内执行了他的查询,而我的查询在5秒内执行,这让我很不安。我不能认同这样一个观点:代码的长度是原来的两倍;它使用的集数是原来的三倍,可以运行得更快。

我在Sybase上进行了测试。我已经有了一个基准结构,它允许非常相似的代码被测试(我的表上的测试不需要连接两个表,但我将其包括在内,以便尽可能接近OP的问题)。200万客户交易(动物)遍布100家客户(生产商)。也就是说,这两个查询都从2.0m数据点或100 x 20000个标量生成相同的结果集(100个客户(生产者)乘以3列)。让我们准确地识别它们,以便更好地理解和比较它们。首先是所用结构的DDL:

Link to DDL

1_set_scalar_子查询

为可用的基准结构呈现的与上面代码完全相同的代码:

• 根据操作说明, 结构 要求(1)是客户(生产商)列表。
• 这很容易翻译成一个简单的查询: SELECT ... FROM Customer (Producer)
• 接下来,我们需要另外两列,具体内容(1)re-customer(producer),这是可以从数据库派生(不容易作为物理列提供)的数据。
• 这很容易做到(2)两个标量子查询查询

Link to 1_Set_Scalar_Subquery Code & Timings

• 1083、1073、1103、1073、1080毫秒

• Sybase统计显示3次扫描:
  客户(生产商)扫描一次
  customerTransaction(animal)扫描了两次(每个标量子查询一次)

3_set_inline_查询

与Tony代码完全相同的代码,用于可用的基准结构:

• 据我所知,从托尼的评论来看,他确实采取了行动;把两个已知的集合 FROM 子句;连接它们;产生第三个集合(第三个外部集合 SELECT )暴露在外的

  • 当我输入代码时,我意识到 1_set_scalar_子查询 在另一方面效率更高,因为它没有2 x GROUP BYs 存在于2个原始集合中的(即原始集合开始时效率低下;并且可以改进)

Link to 3_Set_Inline_Query Code & Timings

• 1820、1850、1846、1843、1850毫秒
  那就是 慢70% .

• Sybase统计显示9次扫描:
  客户(生产商)扫描两次(外部查询) CustomerTransaction(动物)扫描了两次(两个内联集)
  工作台2扫描3次(合并集)
  每扫描一次工作台1和3(用于 分组依据 )

当然,在繁忙的服务器上,70%的差异会被夸大。

3_set_inline_query_improved

由于OP提供的原始集合效率很低,也就是说,它可以很容易地改进,2 x 分组依据 它要求工作表可以被删除,等等;与标量子查询相比,在保持内联的情况下,生成更合理的比较。当然,这意味着,不是操纵一个大的连接集,然后 GROUP BY 获取聚合;使用(1)和(2)仅操作所需的集合,并使用标量子查询填充聚合。

这样做的目的是测试我的语句的准确性,即Sybase以相同的效率执行标量子查询和内联查询。至少现在我们比较了两个好的代码段,每个代码段的结构完全不同,而不是一个好的代码段和一个差的代码段。

Link to 3_Set_Inline_Query_Improved Code & Timings

• 1103、1073、1103、1073、1080毫秒
  实际上是这样 完全相同的 到1_设置_scalar_子查询

• Sybase统计显示4次扫描:
  客户(生产商)扫描一次
  customerTransaction(animal)扫描了两次(每个标量子查询一次;即使有两个内联集)
  客户(生产商)再次扫描(用于最外层查询)

• 因此(在这个简单的测试中)可以证明Sybase执行的内联查询与标量子查询完全相同。在这种情况下,它是3组对1组,我认为乐观主义者在 归一化 为了产生相同的性能结果,查询(不是像Tony查询的那样扁平化,我响应)。

• 这个练习证明的另一件事是采取(1)、(2)方法的重要性。假设即使是合理的规范化,关系数据库中的数据最好使用关系的、面向集的思想框架来处理。这将导致更简单、更高效的代码。当然,更小、更简单、规范化的代码性能更好。

除了甲骨文,由于它的特殊性和局限性,人们必须避免这种情况,并且使用已知不会表现糟糕的结构。

比较2010年12月1日

托尼公布了他的测验结果。他们自言自语,结案了。

然而,他提出了一个问题,即尽管在这个特定的情况下(这个问题),Oracle执行1_set_scalar_子查询的速度几乎是3_set_inline_查询的两倍,因为count()是从索引服务的,而不访问表,通常Oracle执行inline q查询比标量子查询好得多,并测试sum(),以检查访问表的效果。

因此,我在Sybase中运行了相同的测试,count()改为sum()。与Oracle一样,count()是一个覆盖的查询,但是sum()现在要求访问表。

• (稍后结论,当托尼发布了一些缺失的细节。令我困扰的一件事是,Sybase在2秒内处理了200万行;Oracle即使是在最好的情况下,也无法在6秒内处理1000++行的相同工作(表填充待定)。

• 托尼已经发布了一些遗漏的细节,但也打开了新的问题,我在我对他的评论中发现了这些问题。因此,得出明确结论还为时过早。

• 到目前为止,在类似的测试中(对我来说不够接近),在类似的系统上(OK Sybase System有两个额外的软件层,不是服务器):

  • Sybase在 1.1和1.5秒 ;
    Oracle在 3.5和5.7秒 .

  • Sybase的总和 2.1和3.0秒 ;
    甲骨文公司 未完成 子查询并在 79秒
    (更多信息请求待定)。

  • 在最好的情况下(子查询没有完成,所以我们不能计算),按照Oracle的要求使用糟糕的代码, Oracle比Sybase慢26倍 .

  • 当Sybase编码器使用关系集理论时,速度慢了40倍。

1_set_scalar_subquery_sum

与1_set_scalar_subquery_sum相同,count()改为sum()。

Link to 1_Set_Scalar_Subquery_SUM Code & Timings

• 2173、2153、2163、2153、2173毫秒

• Sybase统计显示3次扫描:
  客户(生产商)扫描一次
  customerTransaction(animal)扫描了两次(每个标量子查询一次)

3_设置_inline_查询_和

与3_set_inline_query相同,count()改为sum()。

Link to 3_Set_Inline_Query_SUM Code & Timings

• 3033、2993、3033、2993、3013毫秒
  那就是 慢38% .

• Sybase统计显示9次扫描:
  客户(生产商)扫描两次(外部查询) CustomerTransaction(动物)扫描了两次(两个内联集)
  工作台2扫描3次(合并集)
  每扫描一次工作台1和3(用于 分组依据 )

当然,在繁忙的服务器上,38%的差异会被夸大。

3_set_inline_query_improved_sum

与改进的3_set_inline_query_相同,count()改为sum()。

Link to 3_Set_Inline_Query_Improved_SUM Code & Timings

• 2183、2180、2183、2143、2143毫秒
  实际上是这样 完全相同的 到1_设置_scalar_子查询

• Sybase统计显示4次扫描:
  客户(生产商)扫描一次
  customerTransaction(animal)扫描了两次(每个标量子查询一次;即使有两个内联集)
  客户(生产商)再次扫描(用于最外层查询)

• 在Sybase上下文中有一件事。我进一步证明了Sybase乐观器规范化查询的断言,即它处理标量子查询以及内联视图。不良代码(3_set_inline_query 未改进 )运行较慢,但只成比例地较慢,没有显著差异。

我认为这应该是一个单独的答案,因为它与你最初的问题有关,而不是你所说的问题。我接受你的 还是我做错了什么? 在另一个地方。

由于我和托尼一起练习,这需要检查你的两套原稿,所以很明显,它们效率很低,可以根据我之前的答案(1),(2)加以改进。考虑和关注Oracle的障碍,您需要解决这些障碍,干扰和抑制解决查询的面向集方法。

1. 因此,我们将把这一点放在一边,采取(1)、(2)的方法。另一种说法是,如果你从坏苹果开始,然后简单地扩展它们(这是你的特定问题),那么坏苹果的数量会增加四倍,这在测试中得到了证明。

2. 我把大结果集的投影放在一边,然后 GROUPing 就像你一样。因为您想要一个生产者+列表,所以我使用它作为查询的基本结构。然后我用标量子查询填充聚合。这个 GROUP BY 而处理这些问题所需的工作台也被淘汰了。

3. 另一种说法是,我已经规范了您最初的查询。

4. 我假设你需要加入 AnimalsBought 用于存在性检查;否则(因为在 COUNT() )可以移除。

我怀疑它会比您最初的查询更快,尽管我听说Oracle不能很好地处理标量子查询,因为这样可以避免处理大的集合;但是我不能在Oracle上测试它。

SELECT  Producers.name Prod, 
        ( SELECT count(Animals.idanimal)
              FROM  Animals,
                    AnimalsBought
              WHERE (p.nif = Animals.owner)
              AND   (Animals.idanimal = AnimalsBought.idanimal) 
              ) AnimalsBought
    FROM  Producers p;

SELECT  Producers.name Prod, 
        ( SELECT count(Animals.idanimal)
              FROM  Animals,
                    AnimalsExploration
              WHERE (p.nif = Animals.owner)
              AND   (Animals.idanimal = AnimalsExploration.idanimal) 
              ) AnimalsExploration
    FROM  Producers p;

如果这对你有用,那自然会得到你所说问题的答案:

SELECT  Producers.name Prod, 
        ( SELECT count(Animals.idanimal)
              FROM  Animals,
                    AnimalsBought
              WHERE (p.nif = Animals.owner)
              AND   (Animals.idanimal = AnimalsBought.idanimal) 
              ) AnimalsBought,
        ( SELECT count(Animals.idanimal)
              FROM  Animals,
                    AnimalsExploration
              WHERE (p.nif = Animals.owner)
              AND   (Animals.idanimal = AnimalsExploration.idanimal) 
              ) AnimalsExploration
    FROM  Producers p;

如果你被空值和spinster困扰,那么mcha的答案是最好的。

SELECT A.producer, COUNT(A.firstquerycolumn), COUNT(B.secondquerycolumn) FROM 
TAB1 A, TAB2 B
WHERE A.producer = B.producer 
GROUP BY A.firstquerycolumn, B.secondquerycolumn

这就是你要的?

据我所知,您需要一个查询,它将向您显示:

PRODUCER            FirstQueryColumn       SecondQueryColumn      
------------------------------------------------------------
aaaaaaaaaaaa         1                      2
bbbbbbbbbbb          1                      1

您可以尝试如下操作:

SELECT (SELECT Producers.name Prod
          FROM AnimalsBought, Animals, Producers
         WHERE (AnimalsBought.idanimal = Animals.idanimal)
           AND (Animals.owner = Producers.nif)
         GROUP BY Producers.name) as Producers,
       (SELECT COUNT(Animals.idanimal) AnimalsBought
          FROM AnimalsBought, Animals, Producers
         WHERE (AnimalsBought.idanimal = Animals.idanimal)
           AND (Animals.owner = Producers.nif)
         GROUP BY Producers.name) as firstQuery,
       (SELECT COUNT(Animals.idanimal) AnimalsExploration
          FROM AnimalsExploration, Animals, Producers
         WHERE (AnimalsExploration.idanimal = Animals.idanimal)
           AND (Animals.owner = Producers.nif)
         GROUP BY Producers.name) as secondQuery
  FROM DUAL

当然,你可以优化它,这只是一个想法:)

你想把桌子联合起来吗?(把桌子放在一起)
还是要加入这些表格?(把桌子并排放)

union和join都可以生成您想要的输出格式,但是它们是非常不同的。

由于它们的列不匹配,您需要插入空位置保持器以进行联合。

在不详细讨论查询的情况下,我认为以下内容可能会有所帮助。

Select A.Producer, A.FirstQueryColumn, B.SecondQueryColumn
From
(
   Select Producer, FirstQueryColumn, '' As SecondQueryColumn
   From TableA
) AS A
Inner Join
(
   Select Producer, '' as FirstQueryColumn, SecondQueryColumn
   From TableA
) AS B ON A.Producer = B.Producer