本文介绍了SQL - 显示计数最大的条目?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
($ CHAR(13),docname CHAR(30));假设我有这样的表格,那么我将如何显示具有大多数患者?如果最多的是三个,两个医生有三个病人,那么我会显示他们的两个名字。
这会得到最大患者数:
SELECT MAX(count)
FROM(SELECT COUNT(docname)FROM doctor GROUP docname)a;
这是所有的医生和他们有多少患者:
选择docname,COUNT(docname)FROM doctor GROUP BY名称;
现在我无法弄清楚如何合并它们以仅列出拥有最大的患者。
谢谢。
SELECT docname,COUNT(*)FROM doctor GROUP BY name HAVING COUNT(*)=
(SELECT MAX c)FROM
(SELECT COUNT(patient)as c
FROM doctor
GROUP BY docname))
另一方面,如果您只需要第一个条目,那么
SELECT docname,COUNT (docname)FROM doctor
GROUP BY名称
ORDER BY COUNT(docname)DESC LIMIT 1;
CREATE TABLE doctor( patient CHAR(13), docname CHAR(30) );
Say I had a table like this, then how would I display the names of the doctors that have the most patients? Like if the most was three and two doctors had three patients then I would display both of their names.
This would get the max patients:
SELECT MAX(count)
FROM (SELECT COUNT(docname) FROM doctor GROUP BY docname) a;
This is all the doctors and how many patients they have:
SELECT docname, COUNT(docname) FROM doctor GROUP BY name;
Now I can't figure out how to combine them to list only the names of doctors who have the max patients.
Thanks.
解决方案
This should do it.
SELECT docname, COUNT(*) FROM doctor GROUP BY name HAVING COUNT(*) =
(SELECT MAX(c) FROM
(SELECT COUNT(patient) AS c
FROM doctor
GROUP BY docname))
On the other hand if you require only the first entry, then
SELECT docname, COUNT(docname) FROM doctor
GROUP BY name
ORDER BY COUNT(docname) DESC LIMIT 1;
这篇关于SQL - 显示计数最大的条目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!