Mysql需要选择多个条目数量最多的所有值

Question

我的mysql查询

SELECT  count(ICD10) as columnDis, ICD10
FROM(
     SELECT tbl_disease.ICD10, tbl_symptom_disease.symptomCode 
     FROM `tbl_disease`
     JOIN tbl_symptom_disease
     ON tbl_disease.ICD10=tbl_symptom_disease.diseaseCode
     WHERE tbl_symptom_disease.symptomCode='P18' 
     OR tbl_symptom_disease.symptomCode='P19'
    ) AS tbl_ICD10_symCode
GROUP BY ICD10
HAVING columnDis=?????

因此，此查询返回所有具有等于 ?????? 的重复值的 ICD10 条目。

在此示例中，重复项的最大数量为 2，但我想将其用于不同的 sympomCode(s)，在这种情况下，可能有 3 个重复条目出现 5 次，另外 5 个重复条目出现 3 次，1 个重复条目出现 3 次。出现2次，依此类推。所以我需要的只是出现次数最多的重复条目。类似于:

HAVING columnDis=(MAX(columnDis))

但这当然行不通。有人可以帮我吗？

Answer 1

使用子查询获取最大计数，然后将其与主子查询连接

SELECT ICD10
FROM
(
    SELECT MAX(disease_count) AS max_disease_count
    FROM
    (
        SELECT tbl_disease.ICD10, COUNT(tbl_disease.ICD10) AS disease_count
        FROM `tbl_disease`
        JOIN tbl_symptom_disease
        ON tbl_disease.ICD10=tbl_symptom_disease.diseaseCode
        WHERE tbl_symptom_disease.symptomCode='P18' 
        OR tbl_symptom_disease.symptomCode='P19'
        GROUP BY ICD10
    ) sub0
) sub1
INNER JOIN
(
    SELECT tbl_disease.ICD10, COUNT(tbl_disease.ICD10) AS disease_count
    FROM `tbl_disease`
    JOIN tbl_symptom_disease
    ON tbl_disease.ICD10=tbl_symptom_disease.diseaseCode
    WHERE tbl_symptom_disease.symptomCode='P18' 
    OR tbl_symptom_disease.symptomCode='P19'
    GROUP BY ICD10
) sub2
ON sub1.max_disease_count = disease_count