MySQL - 计算来自两个表的记录，按一个字段分组 [英] MySQL - Count records from two tables grouped by one field
问题描述
我有2个表格：
- 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
执行以下操作：
选择d.RepName，
count（d.RepName）DoctorReportsCount，
count h.RepName）HospitalsReportsCount，
d.DateAdded
from doctorreports d
inner连接医院h
在d.RepName = h.RepName
组由d.RepName， d.DateAdded
编辑：
code> select *
from
（
select d.RepName，
count（d.RepName）DoctorReportsCount
，d.dateadded
doctorreports d
group by d.RepName，d.dateadded
）d
left join
（
select h.RepName，
count（h.RepName ）HospitalsReportsCount
，h.dateadded hDateadded
从医院h
组h.RepName，h.dateadded
）h
在d.RepName = h.RepName
请参阅 SQL Fiddle with demo
编辑＃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
请参阅 SQL Fiddle with Demo
如果您不在乎其他日期，那么这将是如何没有日期 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:
- doctorreports with fields:
- DoctorRepID
- RepName
- DoctorName
- DateAdded
- 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:
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
edit:
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
see SQL Fiddle with demo
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 - 计算来自两个表的记录，按一个字段分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
