So fügen Sie eine Spalte in einer Auswahlabfrage mit Rechtsverknüpfung hinzu

Question

Ich versuche eine Möglichkeit zu finden, einem Datenbankanrufdatensatz basierend auf einer Telefonnummernspalte eine Landesvorwahl hinzuzufügen. Ich habe eine Tabelle, die Länder und ihre Vorwahlen (sogenannte Länder) enthält. Ich kann alle Datensätze abfragen und den Ländercode anhängen, aber ich muss in der Lage sein, die Ergebnisse zu filtern und zu paginieren.

Ich arbeite mit einem System, über das ich nicht viel Kontrolle habe, daher sind das Hinzufügen neuer Spalten zu Tabellen oder das Umschreiben großer Codeblöcke keine wirklichen Optionen. Damit muss ich klarkommen.

Landtisch.

USA Anrufprotokoll.

id	Name	Vorwahl
1	Irland	353
2	1

id12Routing-Tabelle.

Datum und Uhrzeit des Starts	Enddatum und Uhrzeit	route_id	Telefonnummer	duration_seconds
2014-12-18 18:51:12	2014-12-18 18:52:12	23	3538700000	60
2014-12-18 17:41:02	2014-12-18 17:43:02	43	18700000	120

id2343

Ich muss den Gesamtwert der Dauer aller eindeutigen Telefonnummern erhalten, die nach Routen-ID und Routennummer gruppiert sind. Jetzt müssen wir diese Ergebnisse jedoch nach Länder-ID gruppieren, damit wir Anrufer nach Land gruppieren können. Ich verwende die folgende MySQL-Abfrage, um den Summenwert der Dauer und der Gesamtzahl der eindeutigen Telefonnummern zu erhalten, alle gruppiert nach Routen-ID und Routennummer. Diese Abfrage wurde vor langer Zeit von einem anderen Entwickler geschrieben.

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;

Ich habe mir die Mühe gemacht, „country_id“ zur rechten Join-Tabelle hinzuzufügen, damit ich nach „country_id“ gruppieren kann.

Ich weiß, dass ich PHP verwenden kann, um jedes Land zu durchlaufen und die Ergebnisse mithilfe einer Where-Klausel zu erhalten, wie unten gezeigt, aber ich kann diese Ergebnisse nicht paginieren oder einfach filtern.

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

Wie füge ich mithilfe der Ländertabelle einer Join-Tabellenabfrage eine Spalte hinzu, die die Länder-ID enthält, damit ich nach Routen-ID, Routennummer und Länder-ID gruppieren kann? Wie in der Tabelle unten gezeigt.

Zahlen	Aktiviert
1234567890	1
0987654321	1

id	Datum und Uhrzeit des Starts	Enddatum und Uhrzeit	route_id	Telefonnummer	duration_seconds	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() 以使这些条件可控制。