MySQL - 计算来自两个表的记录，按一个字段分组

问题描述

我有2个表格：

1. doctorreports包含栏位：
   
   
   
   • DoctorRepID
   
   
   
   
   • 
     
   
   
   • >
     
   • 医院与以下字段：
     
     
     
     • HospitalID
     
     
     • RepName
     
     
     • HospitalName
     
     
     • 
       
     • DoctorName
     
     
     • 添加日期
     
     
   
   

我需要计算每个RepName在doctorreports&医院与DateAdded。

我需要它出现如下：

  RepName | DoctorReportsCount |医院DateAdded 
 | | | 
 John | 15 | 12 | 9/4/2012 
  

医生表中的医生名单中的RepName与医生表中的RepName相同。

@bluefeet这部分是我需要的，但是我们可以将DateAdded字段统一为如果RepName在此日期没有添加任何记录，则DateAdded = 0。例如：

  RepName | DoctorReportsCount |医院DateAdded 
 | | | 
 John | 15 | 12 | 9/4/2012 
 Ann | 9 | 0 | 9/2/2012 
 Tamer | 0 | 12 | 9/1/2012 
  

解决方案

执行以下操作：

编辑：

编辑＃2，如果要返回缺少的天数据，那么我建议创建一个表包含日历日期，那么您可以返回缺少的天的数据。以下应该返回你正在寻找的。建议我为此查询创建一个日历表：

 选择COALESCE（d.drep，''）repname，
 COALESCE（d.DCount，0）DoctorReportsCount，
 COALESCE（h.HCount，0）HospitalsReportsCount，
 c.dt日历添加
日历c 
 left join 
（
 select repname drep，
 count（repname）DCount，
 dateadded ddate 
 from doctorreports 
 group by repname，dateadded 
）d 
 on c.dt = d.ddate 
 left join 
（
选择repname hrep，
 count（repname）HCount，
 dateadded hdate 
从医院
 group by repname，dateadded 
）h 
 on c.dt = h.hdate 
和d.drep = h.hrep 
  

请参阅

如果您不在乎其他日期，那么这将是如何没有日期 table：

  select COALESCE（d.RepName，''）repname，
 COALESCE DoctorReportsCount，0）DoctorReportsCount，
 COALESCE（h.HospitalsReportsCount，0）HospitalsReportsCount，
 COALESCE（p.PharmacyReportsCount，0）PharmacyReportsCount，
 d.dateadded Dateadded 
 from 
（
 select d.RepName，
 count（d.RepName）DoctorReportsCount 
，d.dateadded 
 from doctorreports d 
 group by d.RepName，d.dateadded 
）d 
 left join 
（
 select h.RepName，
 count（h.RepName）HospitalsReportsCount 
，h.dateadded hDateadded 
来自医院h 
 group by h.RepName，h.dateadded 
）h 
 on d.RepName = h.RepName 
 and d.dateadded = h.hDateadded 
 left join 
（
 select p.RepName，
 count（p.RepName）PharmacyReportsCount 
，p.dateadded hDateadded 
来自PharmacyReports p 
 group by p .RepName，p.dateadded 
）p 
 d.RepName = p.RepName 
和d.dateadded = p.hDateadded 
  

请参阅 SQL Fiddle with Demo

I have 2 tables:

1. doctorreports with fields:
   • DoctorRepID
   • RepName
   • DoctorName
   • DateAdded
2. hospitals with fields:
   • HospitalID
   • RepName
   • HospitalName
   • DoctorName
   • DateAdded

I need to count the records added by each RepName in doctorreports & hospitals with the DateAdded.

I need it to appear like this:

RepName | DoctorReportsCount | HospitalsReportsCount | DateAdded
        |                    |                       |
  John  |          15        |          12           | 9/4/2012

RepName in doctorsreports table equal RepName in hospitals table.

@bluefeet This is partially what i need, but can we unify the DateAdded field to be if the RepName hasn't added any records in this date then the DateAdded = 0. For examle :

RepName | DoctorReportsCount | HospitalsReportsCount | DateAdded
        |                    |                       |
  John  |          15        |          12           | 9/4/2012
  Ann   |          9         |           0           | 9/2/2012
Tamer   |          0         |           12          | 9/1/2012

解决方案

Sounds like you are trying to do this:

edit:

edit #2, if you want to return data for days that are missing, then I would advise creating a table to contain calendar dates, then you can return data for days that are missing. The following should return what you are looking for. Be advised, I created a calendar table for this query:

select COALESCE(d.drep, '') repname,
  COALESCE(d.DCount, 0) DoctorReportsCount,
  COALESCE(h.HCount, 0) HospitalsReportsCount,
  c.dt Dateadded
from calendar c
left join
(
  select repname drep,
    count(repname) DCount,
    dateadded ddate
  from doctorreports
  group by repname, dateadded
) d
  on c.dt = d.ddate
left join
(
  select repname hrep,
    count(repname) HCount,
    dateadded hdate
  from hospitals
  group by repname, dateadded
) h
  on c.dt = h.hdate
  and d.drep = h.hrep

see SQL Fiddle with Demo

If you don't care about the other dates, then this is how you would do it without a date table:

select COALESCE(d.RepName, '') repname,
  COALESCE(d.DoctorReportsCount, 0) DoctorReportsCount,
  COALESCE(h.HospitalsReportsCount, 0) HospitalsReportsCount,
  COALESCE(p.PharmacyReportsCount, 0) PharmacyReportsCount,
  d.dateadded Dateadded
from
(
  select d.RepName,
      count(d.RepName) DoctorReportsCount
    , d.dateadded
  from doctorreports d
  group by d.RepName, d.dateadded
) d
left join
(
  select h.RepName,
    count(h.RepName) HospitalsReportsCount
  , h.dateadded hDateadded
  from hospitals h
  group by h.RepName, h.dateadded
)h
  on d.RepName = h.RepName
  and d.dateadded = h.hDateadded
left join
(
  select p.RepName,
    count(p.RepName) PharmacyReportsCount
  , p.dateadded hDateadded
  from PharmacyReports p
  group by p.RepName, p.dateadded
)p
  on d.RepName = p.RepName
  and d.dateadded = p.hDateadded

see SQL Fiddle with Demo

这篇关于MySQL - 计算来自两个表的记录，按一个字段分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！