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) slave 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)...

高洛峰 · Answer

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

PHP中文网 · Answer

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

You can use outer join query

Php8, I'm coming too

Learn website layout in 30 minutes

Shangguan Oracle Beginner to Proficient Video Tutorial

Your first line of UNI-APP code

Flutter from scratch to app launch

Brother Lian New Linux Video Tutorial

AXURE 9 Video Tutorial (Suitable for Product Manager Interactive Product Design UI)

Zero Basic Proficiency PS Video Tutorial

16 day UI video tutorial to get you started

PS Techniques and Slicing Techniques Video Tutorial

Alibaba Cloud Environment Construction and Project Launch Video Tutorial

Overview of Computer Networks - Basic Knowledge that Programmers Must Master

Essential Tutorial for Programmers - HTTP Protocol Explanation

Websocket Video Tutorial