如何在右連接的選擇查詢中新增一列

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() 以使這些條件可控制。