本文介绍了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 - 显示计数最大的条目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-17 00:19