mysql - 使用 UNION ALL 从多个表返回单个结果字段值作为结果数据集中记录的一部分

Question

我有几个表，我想比较数据并返回 CDR 记录中的最后一次调用日期。各表说明如下:

电话号码

包含提供服务的电话号码列表。

YYYY-MM

包含指定为表名称的时间段内的 CDR 记录。 YYYY = 4 位数年份，MM = 2 位数月份。

到目前为止我的查询如下:

SELECT DISTINCT
customerid,
phonenumber,
(SELECT Started FROM (
(SELECT * FROM `2016-08` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-07` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-06` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-05` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-04` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-03` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-02` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-01` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
) AS p ORDER BY p.Started DESC LIMIT 1) `lastcall`
FROM phonenumbers WHERE phonenumbers.dateterminated IS NULL AND
(
    phonenumbers.phonenumber LIKE '800%' OR
    phonenumbers.phonenumber LIKE '855%' OR
    phonenumbers.phonenumber LIKE '866%' OR
    phonenumbers.phonenumber LIKE '877%' OR
    phonenumbers.phonenumber LIKE '888%'
)

上述查询逻辑是我希望实现的结果列表，其中包含我的电话号码表中的所有唯一免费电话号码，以及最近的Started(调用开始的日期和时间)，其中Caller 号码是在我的电话号码表中找到的免费电话号码，或者 Dialed 号码是在我的电话号码表中找到的免费电话号码，前面带有“1”到它。

不幸的是，运行此查询会出现以下错误:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS p ORDER BY p.Started DESC LIMIT 1) lastcall FROM phonenumbers WHERE phon' at line 13

我尝试过对 UNION ALL 数据的结果集进行命名和不命名(在上面的示例中，它被命名为 p 为 AS p)。我尝试的未命名的尝试如下:

SELECT DISTINCT
customerid,
phonenumber,
(SELECT Started FROM (
(SELECT * FROM `2016-08` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-07` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-06` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-05` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-04` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-03` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-02` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
(SELECT * FROM `2016-01` WHERE Dialed = CONCAT('1', phonenumbers.phonenumber) OR Caller = phonenumbers.phonenumber) UNION ALL
) ORDER BY Started DESC LIMIT 1) `lastcall`
FROM phonenumbers WHERE phonenumbers.dateterminated IS NULL AND
(
    phonenumbers.phonenumber LIKE '800%' OR
    phonenumbers.phonenumber LIKE '855%' OR
    phonenumbers.phonenumber LIKE '866%' OR
    phonenumbers.phonenumber LIKE '877%' OR
    phonenumbers.phonenumber LIKE '888%'
)

.. 导致此消息:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY Started DESC LIMIT 1) lastcall FROM phonenumbers WHERE phonenumber' at line 13

同样的错误，稍微不同的上下文，这让我相信(像往常一样)错误是在其他地方，但是消息不清楚并且太笼统，无法破译 MySQL 提示的内容

考虑到查询中指定的所有字段和表都存在，以及我在上面适当描述的上下文，如何调整该查询以使其正常工作？

Answer 1

从最后一个联合中删除“UNION ALL”。即 (SELECT * FROM 2016-01 WHERE Dialed = CONCAT('1', Phonenumbers.phonenumber) OR Caller = Phonenumbers.phonenumber) UNION ALL。