mysql 如何向右连接选择查询添加列

7hiiyaii 于 2023-02-15 发布在 Mysql

关注(0)|答案(1)|浏览(122)

我试图找到一种方法，根据电话号码列将国家代码添加到数据库呼叫记录中。我有一个表，其中包含国家及其拨号代码，称为国家。我可以查询所有记录并在后面添加国家代码，但我需要能够筛选结果并对结果进行分页。
我正在使用一个我无法控制的系统，所以在表中添加新列或重写大块代码并不是一个真正的选择，这是我必须使用的。
国家表。
| 身份证|姓名|拨号代码|
| - ------|- ------|- ------|
| 1个|爱尔兰|三五三|
| 第二章|美国|1个|
呼叫记录表。
| 身份证|开始日期时间|结束日期时间|路由标识|电话号码|持续时间_秒|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 1个|2014年12月18日18时51分12秒|2014年12月18日18时52分12秒|二十三|小行星353870|六十|
| 第二章|2014年12月18日17时41分02秒|2014年12月18日17时43分02秒|四十三|小行星1870|一百二十|
路由表。
| 身份证|数|使能|
| - ------|- ------|- ------|
| 二十三|小行星1234567890|1个|
| 四十三|小零九八七六五四三二一|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子句得到结果，就像下面这样，但是我不能对这些结果进行分页或过滤。
WHERE LEFT(a.phonenumber, strlen($dialling_code)) = $dialling_code
如何使用countries表向连接表查询中添加一列国家ID，以便按route_id、route_number和country_id分组？类似于下表。
| 身份证|开始日期时间|结束日期时间|路由标识|电话号码|持续时间_秒|国家标识|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 1个|2014年12月18日18时51分12秒|2014年12月18日18时52分12秒|二十三|小行星353870|六十|1个|
| 第二章|2014年12月18日17时41分02秒|2014年12月18日17时43分02秒|四十三|小行星1870|一百二十|第二章|

mysql

来源：https://stackoverflow.com/questions/75448187/how-can-i-add-a-column-to-a-right-join-select-query

1条答案

按热度按时间

2w3kk1z51#

从routes到callrecord_join的RIGHT JOIN没有任何意义，因为在子查询中已经有了routes和callrecord之间的INNER JOIN，它位于连接的右侧。
您可以使用您所描述的联接-

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, '%')

这应该稍微便宜一些。
您应该测试countries的连接，以确保您在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()，以使这些条件可优化（假设有合适的索引可用）。

赞(0）回复(0）举报 2023-02-15

我来回答

mysql 如何向右连接选择查询添加列

1条答案

相关问题

热门标签

最新问答