MS SQL词条问题

Question

我有两栏

Item    |   Category

Apple      Fruit
Orange     Fruit
Potatoes   Vegetables

所需产出：

水果

我正试图从数量上得到最高类别的名称，即水果。我编写了一个在MySql上工作的MySql查询。但是，相同的查询会在上引发一个错误。任何提示都将不胜感激。

select Cat from (select count(category) as Total,category as cat from test group by category) as S1 group by S1.Total,S1.Cat having max(s1.total)

当我运行这个时，我会得到一个空的结果。但是，我创建的类似查询在MySql上运行良好：

SELECT s2.cats FROM (SELECT s1.cat AS cats, max(s1.counts) AS countings FROM (SELECT count(category) AS counts, category AS cat from test GROUP BY category) AS s1) AS s2

我得到的错误消息是: Msg 4145、级别15、状态1、第1行--在期望条件的上下文中指定的非布尔类型的表达式，近‘’。

我想避免使用顶部

Answer 1

像这样吗？很丑，但避开托普。同时，还将返回所有具有最大计数的项，而不仅仅是任意项。

假设有列类别的餐桌食品：

select category from Food group by Category having COUNT(*) = 
(select max(cnt) from (select COUNT(*) as cnt from Food group by Category) subq)