带有 Join 和 union all 的 mysql 查询

Question

我有一个来自 4 个表的联合所有查询。从 4 个表中，我从所有 4 个表中获取 Original_APN 的总数。为此，我使用以下查询。

SELECT Original_APN SUM(Quanity) Quanity  FROM (
  SELECT Original_APN, Quanity FROM hs_apn1
  UNION ALL 
  SELECT Original_APN, Quanity FROM hs_apn2
  UNION ALL
  SELECT Original_APN, Quanity FROM hs_apn3
  UNION ALL 
  SELECT Original_APN, Quanity FROM hs_apn4
) a
GROUP BY Original_APN LIMIT 0,4 

下面是我得到的输出:

+---------------+---------+
| Original_APN  | Quanity|
+---------------+---------+
| 0097512135344 |   30.00 |
| 0097512203272 |    2.00 |
| 0097512203296 |    2.00 |
| 0617823119019 |  200.00 |
+---------------+---------+

现在我的要求是我有另一个表格价格。在该表中，我有一个公共(public)列 Original_APN。在价格表中，我有 Original_APN、aritkel、product_name 和价格。下面是我想要的输出。但是它未能将此价格表与我上面的 union all 查询结合起来。

+---------------+---------+---------+--------------+-------+
| Original_APN  | Quanity | Artikel | product_name | price |
+---------------+---------+---------+--------------+-------+
| 0097512135344 |   30.00 | 122     |    TEST1     | 11.00 |
| 0097512203272 |    2.00 |  34     |    TEST2     | 12.45 |
| 0097512203296 |    2.00 | AX1     |    TEST3     | 15.65 |
| 0617823119019 |  200.00 | MN4     |    TEST4     | 22.35 |
+---------------+---------+---------+--------------+-------+

Answer 1

您可以使用表别名，然后像这样加入价格表:

SELECT s.Original_APN, s.Quantity, p.Artikel, p.product_name, p.price FROM (
  SELECT Original_APN, SUM(Quantity) Quantity FROM (
    SELECT Original_APN, Quantity FROM hs_apn1
    UNION ALL 
    SELECT Original_APN, Quantity FROM hs_apn2
    UNION ALL
    SELECT Original_APN, Quantity FROM hs_apn3
    UNION ALL 
    SELECT Original_APN, Quantity FROM hs_apn4
  ) a
  GROUP BY Original_APN
) s
INNER JOIN
prices p
ON s.Original_APN = p.Original_APN