查看mysql哪个表使用最频繁,MySQL：在表中查找最频繁出现的值

I have two tables:

Purchases:

item

001

003

002

001

002

004

003

001

002

Item:

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?

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.

解决方案

Try this:

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.