Mysql one-to-many How to perform multi-condition and multiple statistics on the slave table

Question

Main table User table
id name phone (associated field)

From table call record table
id phone (associated field) ot_phone time type (incoming and outgoing calls) input_time

Contact table
id c_id phone

Company table
c_id name

How to count each person's phone number (list limit 0, 10)
How many times have been called in
How many times have been called out
How many times has the local number
How many times has the foreign number
Total incoming calls How much time
How much total outgoing call time
How many matching companies (mainly this, because the phones in the contact table have the same phone, but correspond to different companies, the left join sum() data is inaccurate)
Matching company calls How much time
......

The main problem is: the phone in the contact table is not unique, there are duplicate phone numbers, for example, a person works in two companies
After leftjoin sum
The company will have duplicate data and the data is inaccurate

Actually, I thought about deduplicating the contact table phone and then joining it with other tables, but this would be extremely slow, taking 50s
The contact table is more than 50,000 yuan

It seems that this cannot be solved with one sql

Final result
is similar to
id name phone in_num (number of incoming calls) out_num (number of outgoing calls) local_phone_num (local number) .......
23 '小白' 15523232323 45 120 30 .....
24 '小红' 18823232323 70 93 41 ...

Answer 1

First of all, you need to have a field that defines local and foreign numbers, and then whether you need to display numbers without call records. If necessary, change the following sql to left join and perform a null judgment on the value in the right table. If not, you can directly Used

select 
    id,name,phone,
    sum(case when type='in' then 1 else 0 end) cnt_in,
    sum(case when type='out' then 1 else 0 end) cnt_out,
    sum(case when iflocal='1' then 1 else 0 end) cnt_local,
    sum(case when iflocal='0' then 1 else 0 end) cnt_nonlocal,
    sum(case when type='in' then input_time else 0 end) alltime_in,
    sum(case when type='out' then input_time else 0 end) alltime_out 
from userlist a join phonelist b 
on a.phone=b.phone group by a.phone;

To add, if the call time in table b is not a statistical int type of minutes, you may need to convert it

Answer 2

Execute the following SQL and you will get the following results: (The expected results in your question are a bit unclear)

id	name	phone	type	count
23	Xiaobai	15523232323	in	14
23	Xiaobai	15523232323	out	287

SQL

SELECT
  a.id,
  a.name,
  b.phone,  -- 坐席自己的电话
  b.type,   -- 呼入呼出 'in' or 'out'
  b.count   -- 次数
FROM phoneList a
  LEFT JOIN
  (SELECT
     phone,
     type,
     count(1) AS count
   FROM phoneLog
   GROUP BY phone, type) b  
  ON a.phone = b.phone

Answer 3

You can use outer join query