php - MySQL Sum(Total) 和 Sum(User) with ROLLUP

Question

我在处理复杂的 MySQL 查询时遇到了一些麻烦。我正在尝试从此查询中获取 3 列。程序的名称、每个程序的站点总数以及每个程序的医师总数(用户总数)。我正在链接 2 个表，procedures 和 tbl_syn_procedure。

只有一个条件可以获得我的医生总数(用户总数)，那就是 procedures.phys_id = '##'，但我不确定如何将其合并到查询中.

我目前有一个大型查询，它提供了我所需数据的一半。

SELECT  
  IFNULL( Synoptic, 'Total Count') AS Synoptic, Total
FROM ( 
SELECT  
  Synoptic, 
  SUM( Data ) AS Total
FROM ( 
      SELECT 
        tbl_syn_procedure.name AS 'Synoptic',
        COUNT(*) AS Data
        FROM procedures 
        JOIN tbl_syn_procedure ON 
        procedures.proc_synoptic_id = tbl_syn_procedure.name_id 
        GROUP BY procedures.proc_synoptic_id 
     ) AS stats 
  GROUP BY Synoptic WITH ROLLUP
) AS stats

这给了我一个包含两列的表格，如下所示:

Synoptic -------------------------- Total

APBI Device Insertion ---------- 1328

MRI Guided Biopsy ------------- 71

Stereotactic Biopsy ------------- 10504

Total Count ---------------------- 11903

但如您所见，它缺少 Physician Total(用户总数)。如果有人可以就如何将第三列添加到查询中给我一些建议。

Answer 1

试试这个

select
count(p.proc_synoptic_id), p.proc_synoptic_id, name, num_phys_procedures
from
procedures p
inner join tbl_syn_procedure on p.proc_synoptic_id = tbl_syn_procedure.name_id
left join
(
    select count(proc_synoptic_id) as num_phys_procedures, proc_synoptic_id from procedures
    where phys_id = ##
    group by proc_synoptic_id
) phys_procedures on p.proc_synoptic_id = phys_procedures.proc_synoptic_id

group by proc_synoptic_id, name;

它首先获取程序总数，然后连接到派生表 phys_procedures，其中包含特定医生的程序数。