SELECT最少计数的行(*) [英] SELECT rows with minimum count(*)

问题描述

比方说，我有一个简单的表，用列进行投票

Let's say i have a simple table voting with columns

id(primaryKey),token(int),candidate(int),rank(int).

我想提取具有特定等级的所有行，并按候选者分组，最重要的是仅使用最小count(*). 到目前为止，我已经达到

I want to extract all rows having specific rank,grouped by candidate and most importantly only with minimum count(*). So far i have reached

SELECT candidate, count( * ) AS count
FROM voting
WHERE rank =1
AND candidate <200
GROUP BY candidate
HAVING count = min( count )

但是，它返回空集.如果我用实际最小值替换min(count)，它将正常工作. 我也尝试过

But,it is returning empty set.If i replace min(count) with actual minimum value it works properly. I have also tried

SELECT candidate,min(count)
FROM (SELECT candidate,count(*) AS count
      FROM voting
      where rank = 1
      AND candidate < 200
      group by candidate
      order by count(*)
      ) AS temp

但是这只导致了1行，我有3行具有相同的最小计数，但是候选者却不同.我希望所有这3行.

But this resulted in only 1 row,I have 3 rows with same min count but with different candidates.I want all these 3 rows.

任何人都可以帮助我.最小计数(*)值相同的行数也有帮助.

Can anyone help me.The no.of rows with same minimum count(*) value will also help.

样本很大，所以我要显示一些虚拟值

Sample is quite a big,so i am showing some dummy values

1 $sampleToken1 101 1

2 $sampleToken2 102 1

3 $sampleToken3 103 1

4 $sampleToken4 102 1

在这里，根据候选者分组时，有3行结合count(*)结果

Here ,when grouped according to candidate there are 3 rows combining with count( * ) results

candidate count( * )

101              1

103              1

102              2

我希望显示前2行，即count(*)= 1或最小值

I want the top 2 rows to be showed i.e with count(*) = 1 or whatever is the minimum

推荐答案

尝试将此脚本用作模式-

Try to use this script as pattern -

-- find minimum count
SELECT MIN(cnt) INTO @min FROM (SELECT COUNT(*) cnt FROM voting GROUP BY candidate) t;

-- show records with minimum count
SELECT * FROM voting t1
  JOIN (SELECT id FROM voting GROUP BY candidate HAVING COUNT(*) = @min) t2
    ON t1.candidate = t2.candidate;

这篇关于SELECT最少计数的行(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！