如何在RIGHT JOIN查询中添加country_id以按国家分组统计通话数据

要实现按route_id、route_number、country_id分组统计，同时支持过滤和分页，核心是在原有查询的子查询中关联countries表，通过电话号码匹配对应的国家区号，直接获取country_id。以下是具体修改方案：

关键思路 #

通过LEFT JOIN关联countries表，匹配通话号码phonenumber开头的国家区号dialling_code。为避免短区号误匹配长区号（比如+1匹配+123开头的号码），需要按区号长度倒序排序，确保取到最精确的匹配结果。

修改后的完整SQL #

SELECT 
    phone_number,
    route_number, 
    COUNT(callrecord_id) AS total_calls, 
    SUM(duration_sec) AS total_duration, 
    callrecord_join.route_id,
    callrecord_join.country_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.number AS route_number,
        c.id AS country_id
    FROM callrecord AS a
    INNER JOIN routes AS b ON a.route_id = b.id
    LEFT JOIN countries AS c 
        ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')
    WHERE DATE(a.startdatetime) BETWEEN '2014-12-18' AND '2014-12-18'
      AND b.enabled = 1
    ORDER BY LENGTH(c.dialling_code) DESC
    LIMIT 18446744073709551615
) AS callrecord_join ON routes.id = callrecord_join.route_id
GROUP BY route_id, route_number, country_id
LIMIT 10 OFFSET 0;

细节说明 #

1. 字段修正：原查询里的b.inboundnumber应为routes表的number字段，b.isenabled应为enabled字段，已同步修正。
2. 日期过滤优化：用DATE(a.startdatetime) BETWEEN替代DATE_FORMAT，写法更简洁且查询效率更高。
3. 精确区号匹配：通过ORDER BY LENGTH(c.dialling_code) DESC和超大数值LIMIT，确保每条通话记录优先匹配最长的对应区号，避免短区号覆盖长区号的情况。
4. 过滤与分页支持：现在可以直接在子查询的WHERE中添加c.id = 目标国家ID进行过滤，原有LIMIT OFFSET分页逻辑完全兼容，无需再用PHP循环处理匹配。

格式适配注意事项 #

如果你的phonenumber和countries表的dialling_code格式不统一（比如号码带+号、区号不带，或者反之），需要调整匹配规则：

• 若号码带+、区号不带：a.phonenumber LIKE CONCAT('+', c.dialling_code, '%')
• 若号码不带前缀、区号带+：a.phonenumber LIKE CONCAT(SUBSTRING(c.dialling_code, 2), '%')

内容的提问来源于stack exchange，提问作者Danny Connolly