如何在右连接的选择查询中添加一列

Question

我正在尝试找到一种方法，将国家/地区代码添加到基于电话号码列的数据库通话记录中。我有一张表，其中包含国家及其拨号代码（称为国家/地区）。我可以查询所有记录并在后面添加国家/地区代码，但我需要能够对结果进行过滤和分页。

我正在使用一个我没有太多控制权的系统，因此向表添加新列或重写大块代码并不是真正的选择。这就是我必须处理的事情。

国家/地区表。

id	姓名	拨号代码
1	爱尔兰	353
2	美国	1

通话记录表。

id	开始日期时间	结束日期时间	route_id	电话号码	duration_秒
1	2014-12-18 18:51:12	2014-12-18 18:52:12	23	3538700000	60
2	2014-12-18 17:41:02	2014-12-18 17:43:02	43	18700000	120

路由表。

id	数字	已启用
23	1234567890	1
43	0987654321	1

我需要获取持续时间的总和值，所有唯一电话号码均按route_id、route_number 分组，但现在我们需要按country_id 对这些结果进行分组，以便我们可以按国家/地区对呼叫者进行分组。我使用下面的 mysql 查询来获取持续时间的总和值、唯一电话号码总数，所有这些值均按route_id、route_number 分组。该查询是另一位开发人员很久以前编写的。

SELECT 
    phone_number,
    route_number, 
    COUNT(callrecord_id) AS total_calls, 
    SUM(duration_sec) AS total_duration, 
    callrecord_join.route_id
FROM routes
RIGHT JOIN (
    SELECT 
        DATE(a.startdatetime) AS call_date, 
        a.id AS callrecord_id, 
        a.route_id AS route_id, 
        a.phonenumber AS phone_number,
        a.duration_seconds as duration_sec,
        b.inboundnumber AS route_number, 
    FROM callrecord AS a
    INNER JOIN routes AS b ON a.route_id = b.id
    WHERE DATE_FORMAT(a.startdatetime, '%Y-%m-%d') >= '2014-12-18' 
    AND DATE_FORMAT(a.startdatetime, '%Y-%m-%d') <= '2014-12-18' 
    AND b.isenabled = 1 
) AS callrecord_join ON routes.id = callrecord_join.route_id
GROUP BY route_id, route_number
LIMIT 10 offset 0;

我已经完成了在右侧联接表中添加country_id 的所有操作，以便我可以按country_id 进行分组。

我知道我可以使用 php 遍历每个国家/地区，并使用 where 子句获取结果，如下所示，但我无法对这些结果进行分页或轻松过滤它们。

左边(a.phonenumber, strlen($dialling_code)) = $dialling_code

如何使用国家/地区表将一列添加到包含国家/地区 ID 的联接表查询中，以便可以按路线 ID、路线编号和国家/地区 ID 进行分组？如下表所示。

id	开始日期时间	结束日期时间	route_id	电话号码	duration_秒	country_id
1	2014-12-18 18:51:12	2014-12-18 18:52:12	23	3538700000	60	1
2	2014-12-18 17:41:02	2014-12-18 17:43:02	43	18700000	120	2

Answer 1

从 routes 到 callrecord_join 的 RIGHT JOIN 没有任何作用，因为您已经在之间有了 INNER JOIN子查询中的 routes 和 callrecord，位于联接的右侧。

您可以使用您所描述的联接 -

JOIN countries c ON LEFT(a.phonenumber, LENGTH(c.dialling_code)) = c.dialling_code

但它会给出相同的结果：

JOIN countries c ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')

应该会稍微便宜一些。

您应该测试对国家/地区的加入，以确保callrecord中的任何号码都不会加入多个国家/地区。某些国际拨号代码不明确，因此这取决于您使用的拨号代码列表。

SELECT a.*, COUNT(*), GROUP_CONCAT(c.dialling_code)
FROM callrecord a
JOIN country c ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')
GROUP BY a.id
HAVING COUNT(*) > 1;

显然，如果您的数据集非常大，您将需要对上述查询进行批处理。

我希望我没有过分简化事情，但根据我对你的问题的理解，查询只是：

SELECT
    r.id AS route_id,
    r.number AS route_number,
    c.name AS country_name,
    SUM(a.duration_seconds) AS total_duration,
    COUNT(a.id) AS total_calls,
    COUNT(DISTINCT a.phonenumber) AS unique_numbers
FROM callrecord AS a
JOIN routes AS r ON a.route_id = r.id
JOIN countries c ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')
WHERE a.startdatetime >= '2014-12-18' 
AND a.startdatetime < '2014-12-19'
AND r.isenabled = 1
GROUP BY r.id, r.number, c.name
LIMIT 10 offset 0;

请注意，假设有合适的索引可用，请注意从 startdatetime 中删除 DATE_FORMAT() 以使这些条件可控制。