从两个表中获取几列的总和 [英] Getting the sum of several columns from two tables

问题描述

我想从两个不同的表中获得几列的总和（这些表共享相同的结构）。

如果我只考虑一个表，我会写这种查询：
$ b $ pre $ SELECT MONTH_REF，SUM（amount1），SUM（amount2）
FROM T_FOO
WHERE卖方= XXX
GROUP BY MONTH_REF;


然而，我想也使用表T_BAR中的数据，然后有一个 select 查询返回以下列：

• MONTH_REF
$ b $ SUM（T_FOO.amount1）+ SUM（T_BAR.amount1）

• SUM（T_FOO.amount2）+ SUM（T_BAR.amount2）

按 MONTH_REF 值分组的所有内容。

请注意，给定的 MONTH_REF 的记录可以在一个表中找到，但不能在另一个表中找到。
在这种情况下，我希望得到 T_FOO.amount1 + 0 （或 0 + T_BAR.amount1


如何写我的SQL查询来获取这些信息？

，我的数据库是Oracle 10g。

解决方案

您可以在组之前合并您的表）：
$ b

  SELECT t.month_ref，SUM（t.amount1），SUM（t.amount2）
 FROM（ SELECT month_ref，amount1，amount2 
 FROM T_FOO 
 WHERE seller = XXX 
 UNION ALL 
 SELECT month_ref，amount1，amount2 
 FROM T_BAR 
 WHERE seller = XXX 
）t 
 GROUP BY t.month_ref 
  

您也可以将表（如果您需要更高级的逻辑）：

  SELECT t.month_ref，SUM（ t.amount1），SUM（t.amount2）
 FROM（SELECT month_r ef，amount1，amount2，seller 
 FROM T_FOO 
 UNION ALL 
 SELECT month_ref，amount1，amount2，seller 
 FROM T_BAR）t 
 where t.seller = XXX 
 GROUP BY t.month_ref 
  

I want to get the sum of several columns from 2 different tables (these tables share the same structure).

If I only consider one table, I would write this kind of query:

SELECT MONTH_REF, SUM(amount1), SUM(amount2)
    FROM T_FOO
    WHERE seller = XXX
    GROUP BY MONTH_REF;

However, I would like to also work with the data from the table T_BAR, and then have a select query that return the following columns:

• MONTH_REF
• SUM(T_FOO.amount1) + SUM(T_BAR.amount1)
• SUM(T_FOO.amount2) + SUM(T_BAR.amount2)

everything grouped by the value of MONTH_REF.

Note that a record for a given MONTH_REF can be found in one table but not in the other table. In this case, I would like to get the sum of T_FOO.amount1 + 0 (or 0 + T_BAR.amount1).

How can I write my SQL query to get this information?

For information, my database is Oracle 10g.

解决方案

You can union your tables before the group by (this is on Oracle, by the way):

SELECT t.month_ref, SUM(t.amount1), SUM(t.amount2)
  FROM (SELECT month_ref, amount1, amount2
          FROM T_FOO
         WHERE seller = XXX
         UNION ALL
        SELECT month_ref, amount1, amount2
          FROM T_BAR
         WHERE seller = XXX
         ) t
 GROUP BY t.month_ref

You may also union the tables with the seller field and filter by it later (in case you need more advanced logic):

 SELECT t.month_ref, SUM(t.amount1), SUM(t.amount2)
   FROM (SELECT month_ref, amount1, amount2, seller
           FROM T_FOO
          UNION ALL
         SELECT month_ref, amount1, amount2, seller
           FROM T_BAR) t
  where t.seller = XXX
  GROUP BY t.month_ref

这篇关于从两个表中获取几列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！