mysql - 连接三个不同的mysql查询表

Question

我想加入三个不同的查询。这是我的疑问

查询1:

SELECT u.user_name,u.first_name 
FROM users u join users_cstm uc on u.id=uc.id_c 
WHERE u.deleted=0?

查询2:

SELECT l.assigned_user_id,count(*) AS lead_count 
FROM lead l GROUP BY l.assigned_user_id?

查询3:

SELECT l.assigned_user_id,AVG(DATEDIFF(l.date_modified,l.date_entered)) AS avgdays 
FROM leads l GROUP BY l.assigned_user_id?

等等。

这是我正在寻找 user_name、first_name、lead_count、avgdays 的结果。来自三个表。

BigQuery join of three tables

我已经尝试了上面链接中的解决方案。但我没有得到结果。

Answer 1

尝试以下查询:-

SELECT u.user_name,u.first_name,
lead_table.lead_count,lead_table.avgdays
FROM users u join users_cstm uc on u.id=uc.id_c
left join 
(SELECT l.assigned_user_id,
        AVG(l.date_modified) AS avgdays,
        count(*) AS lead_count 
FROM leads l GROUP BY l.assigned_user_id) lead_table
on u.id=lead_table.assigned_user_id
WHERE u.deleted=0;