使用子查询进行分组和聚合
使用子查询进行分组和聚合
提问于 2012-08-02 01:35:03
我有一个查询，旨在查找去过一次医院的人数。我所拥有的东西是有效的，但是有没有一种方法可以不使用子查询呢？
SELECT count(*) as counts, hospitals.hospitalname
FROM Patient INNER JOIN
hospitals ON Patient.hospitalnpi = hospitals.npi
WHERE (hospitals.hospitalname = 'X')
group by patientid, hospitalname
having count(patient.patientid) >1
order by count(*) desc这将始终返回正确的行数( 30 )，但不返回数字30。如果我删除group by patientid，那么我会得到返回的整个结果集。
我通过这样做解决了这个问题
select COUNT(*),hospitalname
from
(
SELECT count(*) as counts,hospitals.hospitalname
FROM hospitals INNER JOIN
Patient ON hospitals.npi = Patient.hospitalnpi
group by patientid, hospitals.hospitalname
having count(patient.patientid) >1
) t
group by t.hospitalname
order by t.hospitalname desc我觉得必须有一种比一直使用子查询更优雅的解决方案。如何改进这一点？
sample data from first query
row # revisits
1 2
2 2
3 2
4 2
same data from second, working query
row# hosp. name revisitAggregate
1 x 30
2 y 15
3 z 5简单的一对多的医患关系
回答 2
Stack Overflow用户
回答已采纳
发布于 2012-08-02 01:48:46
这是超级老生常谈的，但你在这里：
SELECT TOP 1
ROW_NUMBER() OVER (order by patient.patientid) as Count
FROM
Patient
INNER JOIN hospitals
ON Patient.hospitalnpi = hospitals.npi
WHERE
(hospitals.hospitalname = 'X')
GROUP BY
patientid,
hospitalname
HAVING
count(patient.patientid) >1
ORDER BY
Count descStack Overflow用户
发布于 2012-08-02 02:03:31
select distinct hospitalname, count(*) over (partition by hospitalname) from (
SELECT hospitalname, count(*) over (partition by patientid,
hospitals.hospitalname) as counter
FROM hospitals INNER JOIN
Patient ON hospitals.npi = Patient.hospitalnpi
WHERE (hospitals.hospitalname = 'X')
) Z
where counter > 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11764554
复制相关文章
相似问题
腾讯云开发者
