mysql - 根据用户id统计出现次数

Question

我对 SQL/MySQL 和 Stackoverflow 还很陌生，我正在尝试通过 iReport 创建查询(虽然我不必使用 iReport) SugarCRM CE。我需要的是创建一个报告，显示链接到特定“用户”(员工)的“推荐”、“语音邮件”、“电子邮件”和“Call_ins”的数量。我目前设置的查询有效；然而，它会多次运行数据，生成 200 多页的报告。这是我目前使用的代码:

SELECT
 ( SELECT COUNT(*) FROM `leads` INNER JOIN `leads_cstm` ON `leads`.`id` = `leads_cstm`.`id_c` WHERE (leadtype_c = 'Referral' AND users.`id` = leads.`assigned_user_id`) ),
 ( SELECT COUNT(*) FROM `leads` INNER JOIN `leads_cstm` ON `leads`.`id` = `leads_cstm`.`id_c` WHERE (leadtype_c = 'VM' AND users.`id` = leads.`assigned_user_id`) ),
 ( SELECT COUNT(*) FROM `leads` INNER JOIN `leads_cstm` ON `leads`.`id` = `leads_cstm`.`id_c` WHERE (leadtype_c = 'Email' AND users.`id` = leads.`assigned_user_id`) ),
 users.`first_name`,users.`last_name`
FROM
 `users` users,
 `leads` leads

如有任何指导，我将不胜感激!

Answer 1

您想使用条件求和。以下使用 MySQL 语法:

SELECT sum(leadtype_c = 'Referral') as Referrals,
       sum(leadtype_c = 'VM') as VMs,
       sum(leadtype_c = 'Email') as Emails,
       users.`first_name`, users.`last_name`
FROM users join
    `leads`
     on  users.`id` = leads.`assigned_user_id` INNER JOIN
     `leads_cstm`
     ON `leads`.`id` = `leads_cstm`.`id_c`
group by users.id;