mysql - 返回 3 个表中常见记录与唯一记录的相加

Question

我有 3 个表 table1、table2 和 table3，它们之间没有任何关系。

mysql>从表1中选择*；

+-----------+--------+---------+
| scname    | sccode | samount |
+-----------+--------+---------+
| CustomerA | C1     |   20000 |
| CustomerB | C2     |   10000 |
+-----------+--------+---------+

sccode是客户主表的外键。

mysql>从表2中选择*；

+--------------+---------+---------------+----------+--------+---------+
| raccname     | ramount | raccname2     | ramount2 | raccid | racc2id |
+--------------+---------+---------------+----------+---------+--------+
| CustomerA    |   10000 | Secured Loans |    10000 | C1     | 5       |
| CustomerB    |   12000 | SupplierB     |    12000 | C2     | S2      |
| Fixed Assets |    1000 | SupplierB     |     1200 | 4      | S2      |
+--------------+---------+---------------+----------+--------+---------+

raccid和racc2id分别是raccname和raccname2的代码，但它们之间没有关系表

mysql>从表3中选择*；

+--------------+----------+---------------+-----------+--------+----------+
| pyaccname    | pyamount | pyaccname2    | pyamount2 |pyaccid | pyacc2id |
+--------------+----------+---------------+-----------+--------+----------+
| SupplierA    |    13000 | Secured Loans |     15000 | S1     | 5        |
| Fixed Assets |    12000 | SupplierB     |     12000 | 4      | S2       |
+--------------+----------+---------------+-----------+--------+----------+

pyaccid 和 pyacc2id 分别是 pyaccname 和 pyaccname2 的代码，但它们之间没有关系表。

我需要像这样的输出

+--------------+---------+
| account      |  amount | 
+--------------+---------+
| CustomerA    |   30000 | 
| CustomerB    |   22000 | 
| Fixed Assets |   13000 | 
| Secured Loans|   25000 | 
| SupplierA    |   13000 |
| SupplierB    |   25200 | 
+--------------+---------+

表示按原样添加公共(public)记录和唯一记录。

Answer 1

尝试这个查询:

 SELECT name, sum(amount) FROM (
    (SELECT scname AS name, samount AS amount FROM table1)
    UNION ALL
    (SELECT raccname AS name, ramount AS amount FROM table2)
    UNION ALL
    (SELECT raccname2 AS name, ramount2 AS amount FROM table2)
    UNION ALL
    (SELECT pyaccname AS name, pyamount AS amount FROM table3)
    UNION ALL
    (SELECT pyaccname2 AS name, pyamount2 AS amount FROM table3)
) p GROUP BY name;