Comment ajouter une colonne dans une requête de sélection avec une jointure à droite

Question

J'essaie de trouver un moyen d'ajouter un indicatif de pays à un enregistrement d'appels de base de données en fonction d'une colonne de numéro de téléphone. J'ai un tableau qui contient les pays et leurs indicatifs téléphoniques (appelés pays). Je peux interroger tous les enregistrements et ajouter le code du pays, mais je dois pouvoir filtrer et paginer les résultats.

Je travaille avec un système sur lequel je n'ai pas beaucoup de contrôle, donc ajouter de nouvelles colonnes aux tableaux ou réécrire de gros morceaux de code ne sont pas vraiment des options. C'est à cela que je dois faire face.

Tableau de campagne.

Etats-Unis Feuille d'enregistrement des appels.

id	Nom	Composez le code
1	Irlande	353
2	1

id12Table de routage.

Date et heure de début	Date et heure de fin	route_id	Numéro de téléphone	durée_secondes
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

J'ai besoin d'obtenir la valeur totale de la durée avec tous les numéros de téléphone uniques regroupés par route_id, route_number, mais nous devons maintenant regrouper ces résultats par country_id afin de pouvoir regrouper les appelants par pays. J'utilise la requête MySQL ci-dessous pour obtenir la valeur totale de la durée, le nombre total de numéros de téléphone uniques, tous regroupés par route_id, route_number. Cette requête a été écrite par un autre développeur il y a longtemps.

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;

J'ai fait tout le travail d'ajout de country_id à la table de jointure de droite afin de pouvoir regrouper par country_id.

Je sais que je peux utiliser php pour parcourir chaque pays et obtenir les résultats en utilisant une clause Where comme indiqué ci-dessous, mais je ne peux pas paginer ces résultats ou les filtrer facilement.

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

Comment puis-je ajouter une colonne à une requête de table de jointure contenant l'ID du pays à l'aide de la table des pays afin de pouvoir regrouper par ID d'itinéraire, numéro d'itinéraire et ID de pays ? Comme le montre le tableau ci-dessous.

Nombres	Activé
1234567890	1
0987654321	1

id	Date et heure de début	Date et heure de fin	route_id	Numéro de téléphone	durée_secondes	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

Depuis routes 到 callrecord_join 的 RIGHT JOIN 没有任何作用，因为您已经在之间有了 INNER JOIN子查询中的 routes 和 callrecord, sur le côté droit de la jointure.

Vous pouvez utiliser la jointure que vous avez décrite -

JOIN countries c ON LEFT(a.phonenumber, LENGTH(c.dialling_code)) = c.dialling_code

Mais cela donnera le même résultat :

JOIN countries c ON a.phonenumber LIKE CONCAT(c.dialling_code, '%')

Cela devrait être légèrement moins cher.

Vous devez vérifier qu'aucun numéro dans 国家/地区的加入，以确保callrecord ne rejoindra plusieurs pays. Certains indicatifs de numérotation internationale sont ambigus, cela dépend donc de la liste d'indicatifs de numérotation que vous utilisez.

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;

Évidemment, si votre ensemble de données est très volumineux, vous devrez regrouper la requête ci-dessus.

J'espère ne pas trop simplifier les choses, mais d'après ma compréhension de votre question, la requête est simplement :

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;

Notez que, en supposant qu'un index approprié soit disponible, il convient de veiller à ce que startdatetime 中删除 DATE_FORMAT() rende ces conditions contrôlables.