mysql - SQL 嵌套查询作业 SQL Fiddle

Question

找到成员(患者)数量最多的保险公司。

这就是数据库，我第一次尝试更接近:

http://sqlfiddle.com/#!3/4b2ed/27

SELECT  P.cid AS cid, COUNT ( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid

我如何获得最大计数p？

Answer 1

只需按患者数量降序排列您的结果，并且只取第一条记录。

SELECT  P.cid AS cid, COUNT( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid
ORDER BY    CountP DESC
LIMIT 1

如果不止一家保险公司可能拥有最大数量的成员，而您想要获取所有这些成员，则需要使用子查询:

SELECT  P.cid AS cid, COUNT( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid
HAVING      CountP = (
    SELECT   COUNT(pid) AS c
    FROM     Patient
    GROUP BY cid
    ORDER BY c DESC
    LIMIT 1
);

在您在评论中描述的奇怪情况下，无法使用 LIMIT，您可以:

SELECT  P.cid AS cid, COUNT( P.pid ) AS CountP
FROM        Patient P
GROUP BY    P.cid
HAVING      CountP = (SELECT MAX(d.c) FROM (
    SELECT   COUNT(pid) AS c
    FROM     Patient
    GROUP BY cid
) AS d);