如何从SQL中的计数中选择最大值

That is a picture of my table.

那是我桌子的照片。

I must select "Fastanumer" of all cars where "Tegund" is the most common value (which is Toyota in this example)

我必须选择“Fastanumer”，其中“Tegund”是最常见的值(在本例中是丰田)

This is the code i tried

这是我试过的代码

SELECT Fastanumer FROM  `Bill` 
WHERE Tegund =  
(SELECT MAX(y.cnt) FROM (SELECT COUNT(Tegund) AS cnt FROM Bill ) AS y)

Which i had to work pretty hard to figure out only to end up beating myself in the head over the fact that MAX will only turn into a number. (And since Tegund isn't a list of numbers...)

我必须非常努力地去搞清楚，结果却因为MAX只会变成一个数字而打了自己一顿。(既然Tegund不是一个数字列表……)

Is this even possible? How can i do this?

这是可能吗?我该怎么做呢?

4 个解决方案

#1

I guess it should work this way:

我想应该是这样的:

SELECT Fastanumer 
FROM  `Bill` 
WHERE Tegund = (
    SELECT Tegund 
    FROM (
        SELECT Tegund,COUNT(*) FROM Bill GROUP BY Tegund ORDER BY COUNT(*) DESC LIMIT 1
    ) t1
)

Or even like this:

甚至是这样的:

SELECT Fastanumer 
FROM  `Bill` 
WHERE Tegund = (
    SELECT Tegund FROM Bill GROUP BY Tegund ORDER BY COUNT(*) DESC LIMIT 1
)

#2

Here's my solution:

这是我的解决方案:

SELECT Bill.*
FROM Bill
WHERE Tegund IN (
  SELECT Tegund
  FROM Bill
  GROUP BY Tegund
  HAVING COUNT(*) = (
    SELECT MAX(cnt) FROM (
      SELECT COUNT(*) cnt
      FROM Bill
      GROUP BY Tegund
    ) s
  )
)

A little more complicated than others, but if more than one Tegund shares the same number of rows, this query will show all Tegunds which are the most common.

稍微复杂一点，但是如果不止一个Tegund共享相同数量的行，这个查询将显示所有最常见的Tegunds。

Please see fiddle here or here.

请把小提琴放在这里或这里。

#3

What you want to do first is figure out which Tegund appears the most in your table. That is what the subquery is doing. Then you will select the Fastanumer which matches that Tegund.

你首先要做的是弄清楚你的桌子上最显眼的是什么Tegund。这就是子查询的作用。然后您将选择与Tegund匹配的Fastanumer。

SELECT DISTINCT Fastanumer 
FROM 'BILL'
WHERE Tegund = (
    SELECT TOPT 1 Tegund, 
                  COUNT(*) as Count
    FROM `BILL`
    GROUP BY Tegund
    ORDER BY Count DESC)

#4

Depends on the DB (and associated SQL). Try

取决于DB(以及相关的SQL)。试一试

select fastanumber from bill
    inner join
(select count(*) as cnt, tegund from Bill group by tegund) grpby
    on bill.tegund = grpby.tegund
    and grpby.cnt = (select max(cnt) from (select count(*) as cnt, tegund from Bill group by tegund))

#1