Cara menambah lajur dalam pertanyaan pilih dengan gabungan kanan

Question

Saya cuba mencari cara untuk menambahkan kod negara pada rekod panggilan pangkalan data berdasarkan lajur nombor telefon. Saya mempunyai jadual yang mengandungi negara dan kod dailan mereka (dipanggil negara). Saya boleh menanyakan semua rekod dan menambahkan kod negara, tetapi saya perlu dapat menapis dan menomborkan keputusan.

Saya sedang bekerja dengan sistem yang saya tidak mempunyai banyak kawalan, jadi menambahkan lajur baharu pada jadual atau menulis semula sebahagian besar kod bukanlah pilihan sebenarnya. Itu yang saya perlu hadapi.

Meja desa.

USA Lembaran rekod panggilan.

id	Nama	Kod Dail
1	Ireland	353
2	1

id12Meja laluan.

Tarikh dan masa mula	Masa tarikh tamat	route_id	Nombor telefon	tempoh_saat
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

Saya perlu mendapatkan nilai jumlah tempoh dengan semua nombor telefon unik yang dikumpulkan mengikut route_id, route_number, tetapi sekarang kita perlu mengumpulkan hasil ini mengikut country_id supaya kita boleh mengumpulkan pemanggil mengikut negara. Saya menggunakan pertanyaan mysql di bawah untuk mendapatkan nilai jumlah tempoh, jumlah nombor telefon unik, semuanya dikumpulkan mengikut route_id, route_number. Pertanyaan ini telah ditulis oleh pembangun lain lama dahulu.

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;

Saya telah melakukan semua kerja menambah country_id pada jadual penyertaan kanan supaya saya boleh mengumpulkan mengikut country_id.

Saya tahu saya boleh menggunakan php untuk lelaran melalui setiap negara dan mendapatkan keputusan menggunakan klausa where seperti yang ditunjukkan di bawah, tetapi saya tidak boleh menomborkan hasil ini atau menapisnya dengan mudah.

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

Bagaimana cara menambahkan lajur pada pertanyaan jadual gabungan yang mengandungi ID negara menggunakan jadual negara supaya saya boleh mengumpulkan mengikut ID laluan, nombor laluan dan ID negara? Seperti yang ditunjukkan dalam jadual di bawah.

Nombor	Didayakan
1234567890	1
0987654321	1

id	Tarikh dan masa mula	Masa tarikh tamat	route_id	Nombor telefon	tempoh_saat	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

Dari routes 到 callrecord_join 的 RIGHT JOIN 没有任何作用，因为您已经在之间有了 INNER JOIN子查询中的 routes 和 callrecord, di sebelah kanan join.

Anda boleh menggunakan gabungan yang anda nyatakan -

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

Tetapi ia akan memberikan hasil yang sama:

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

Ia sepatutnya lebih murah sedikit.

Anda harus menguji bahawa mana-mana nombor di 国家/地区的加入，以确保callrecord tidak akan menyertai berbilang negara. Sesetengah kod dailan antarabangsa adalah samar-samar, jadi ia bergantung pada senarai kod dailan yang anda gunakan.

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;

Jelas sekali, jika set data anda sangat besar, anda perlu menyusun pertanyaan di atas.

Saya harap saya tidak terlalu memudahkan perkara, tetapi dari pemahaman saya tentang soalan anda, pertanyaannya hanyalah:

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;

Perhatikan bahawa, dengan mengandaikan indeks yang sesuai tersedia, penjagaan harus diambil daripada startdatetime 中删除 DATE_FORMAT() untuk menjadikan keadaan ini boleh dikawal.