So fügen Sie eine Spalte in einer Auswahlabfrage mit Rechtsverknüpfung hinzu
P粉5641921312024-01-10 18:08:33
0
1
373
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.
id
Name
Vorwahl
1
Irland
353
2
USA
1
Anrufprotokoll.
id
Datum und Uhrzeit des Starts
Enddatum und Uhrzeit
route_id
Telefonnummer
duration_seconds
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
Routing-Tabelle.
id
Zahlen
Aktiviert
23
1234567890
1
43
0987654321
1
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.
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.
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;
从
routes到callrecord_join的RIGHT JOIN没有任何作用，因为您已经在之间有了INNER JOIN子查询中的routes和callrecord，位于联接的右侧。您可以使用您所描述的联接 -
但它会给出相同的结果：
应该会稍微便宜一些。
您应该测试对
国家/地区的加入，以确保callrecord中的任何号码都不会加入多个国家/地区。某些国际拨号代码不明确，因此这取决于您使用的拨号代码列表。显然，如果您的数据集非常大，您将需要对上述查询进行批处理。
我希望我没有过分简化事情，但根据我对你的问题的理解，查询只是：
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()以使这些条件可控制。