mysql - 如何从更多表中获取总数？

Question

我想合并三个报告。我想在每个表(smr_trn_tsalesenquiry、smr_trn_treceivequotation、smr_trn_tsalesorder)中进行计数，三个表的总计数，并使用来自 hrm_mst_temployee 的 where 条件 employee_gid，按以上三个表中的所有主键分组。

select count(*), 
(
   select count(x.enquiry_gid) 
   from smr_trn_tsalesenquiry x 
   where x.created_by=a.employee_gid
) as enquiry_count, 
(
   select count(y.quotation_gid) 
   from smr_trn_treceivequotation y 
   where y.created_by=a.employee_gid
) as quotation_count, 
(
   select count(z.salesorder_gid)
   from smr_trn_tsalesorder z 
   where z.created_by=a.employee_gid and z.salesorder_status not in('SO Amended','Cancelled','Rejected')
) as sales_count 
from hrm_mst_temployee a 
group by a.employee_gid;

Answer 1

您可以通过传递 employee_id 和从中返回分组计数。

这link将帮助您了解如何使用函数