我有3个表table1
,table2
并且table3
不具有它们之间的任何关系。
mysql>从table1选择*;
+-----------+--------+---------+
| scname | sccode | samount |
+-----------+--------+---------+
| CustomerA | C1 | 20000 |
| CustomerB | C2 | 10000 |
+-----------+--------+---------+
sccode是客户主表的外键。
mysql> select * from table2;
+--------------+---------+---------------+----------+--------+---------+
| 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>从table3选择*;
+--------------+----------+---------------+-----------+--------+----------+
| 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 |
+--------------+---------+
表示按原样添加通用记录和唯一记录。
试试这个查询:
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句