计算来自两个表的记录，这些表按一个字段分组 [英] Count records from two tables grouped by one field

问题描述

我有2张桌子:

1. doctorreports与字段:
   • DoctorRepID
   • RepName
   • DoctorName
   • DateAdded

1. doctorreports with fields:
   • DoctorRepID
   • RepName
   • DoctorName
   • DateAdded

• 医院ID
• RepName
• 医院名称
• DoctorName
• DateAdded

我需要计算Doctorreports&中每个RepName添加的记录带有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

Doctorsreports表中的RepName等于医院表中的RepName.

RepName in doctorsreports table equal RepName in hospitals table.

@bluefeet这部分是我所需要的，但是如果RepName在此日期中未添加任何记录，那么DateAdded = 0，我们是否可以统一DateAdded字段.例如:

@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 example :

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:

select d.RepName, count(d.RepName) DoctorReportsCount, count(h.RepName) HospitalsReportsCount, d.DateAdded from doctorreports d inner join hospitals h on d.RepName = h.RepName group by d.RepName, d.DateAdded

select d.RepName, count(d.RepName) DoctorReportsCount, count(h.RepName) HospitalsReportsCount, d.DateAdded from doctorreports d inner join hospitals h on d.RepName = h.RepName group by d.RepName, d.DateAdded

select * 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

select * 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

编辑#2，如果要返回缺少的日期的数据，那么我建议创建一个包含日历日期的表，然后可以返回缺少的日期的数据.以下内容应返回您要查找的内容.请注意，我为此查询创建了一个日历表:

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

请参见带有演示的SQL提琴

如果您不在乎其他日期，那么这就是在没有date表的情况下如何进行的操作:

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

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