MySQL:在表中查找最频繁出现的值 [英] MySQL: Finding most frequently occuring values in table

问题描述

我有两个表:

购买:

item
001
003
002
001
002
004
003
001
002

项目:

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

我需要在购买表中找到最频繁出现的(多个)商品，并输出该商品的名称.我的查询中应该有什么?

I need to find the (multiple) items that occur most frequently in the purchases table, and output the name of the item. What should I have in my query?

更新(使用MAX和子查询)

UPDATE (using MAX and subqueries)

我可以通过以下方式获得最高计数:

I can get the highest count by:

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

现在，我只需要一种获取具有该计数的项目名称的方法.

Now I just need a way to get the names of the items that have that count.

推荐答案

尝试一下:

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
 )

我相信这一定是更好的方法，但这应该可行.

I'm sure it must be a better way to do this, but this should work.

这篇关于MySQL:在表中查找最频繁出现的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！