MySQL:完善“查找表中最常出现的值”的答案

Question

昨天发布于:

MySQL: Finding most frequently occuring values in table

但想知道是否有办法改进答案，因为您不必在最后重复代码来获取MAX(COUNT)

问题

我有两个表:

购买:

item
001
003
002
001
002
004
003
001
002

项目:

id    |   name
001   |   Item 1
002   |   Item 2
003   |   Item 3
004   |   Item 4

预期输出:

item     name
001      Item 1
002      Item 2

我需要找到购买表中最常出现的(多个)商品，并输出商品的名称。我的查询中应该包含什么？

到目前为止我得到的答案

SELECT t.cnt, t.name FROM 
 (SELECT COUNT(*) AS "cnt", item.name 
  FROM purchases 
  LEFT JOIN item ON item.id = purchases.item 
  GROUP BY item.name) t
WHERE t.cnt = (SELECT MAX(t2.cnt) FROM 
 (SELECT COUNT(*) AS "cnt"
   FROM purchases 
   LEFT JOIN item ON item.id = purchases.item 
   GROUP BY item.name) t2
 )

Answer 1

您必须首先获取最大计数，然后使用它来过滤匹配的项目:

SELECT   i.*
FROM     purchases p
    JOIN item i ON i.id = p.item
GROUP BY i.id
HAVING   COUNT(*) = (
           SELECT   COUNT(*)
           FROM     purchases
           GROUP BY item
           ORDER BY COUNT(*) DESC
           LIMIT    1
         )

请注意，此查询依赖于 id 作为 item 表的 PK(或至少在该表中是唯一的)，原因有两个:

1. 它确保JOIN不会对外部查询中的COUNT(*)产生不利影响；和

2. 它使您能够从 item 中确定地选择 *，尽管进行分组操作(其他 RDBMS 平台不允许这样做，但需要您分组每个非聚合输出列，但 MySQL 提供此“功能”作为性能改进)。