MySQL 如何在 WHERE 子句中使用实际行数 (COUNT(*)) 而不编写与子查询相同的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13107671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use actual row count (COUNT(*)) in WHERE clause without writing the same query as subquery?
提问by nawfal
I have something like this:
我有这样的事情：
SELECT id, fruit, pip
FROM plant
WHERE COUNT(*) = 2;
This weird query is self explanatory I guess. COUNT(*)here means the number of rows in planttable. My requirement is that I need to retrieve values from specified fields only if total number of rows in table = 2.This doesn't work but: invalid use of aggregate function COUNT.
我猜这个奇怪的查询是不言自明的。COUNT(*)这里表示plant表中的行数。我的要求是，仅当表中的总行数 = 2 时，我才需要从指定字段中检索值。这不起作用，但是：invalid use of aggregate function COUNT。
I cannot do this:
我不能做到这一点：
SELECT COUNT(*) as cnt, id, fruit, pip
FROM plant
WHERE cnt = 2;
for one, it limits the number of rows outputted to 1, and two, it gives the same error: invalid use of aggregate function.
一方面，它将输出的行数限制为 1，另一方面，它给出了相同的错误：invalid use of aggregate function.
What I can do is instead:
我可以做的是：
SELECT id, fruit, pip
FROM plant
WHERE (
SELECT COUNT(*)
FROM plant
) = 2;
But then that subquery is the main query re-run. I'm presenting here a small example of the larger part of the problem, though I know an additional COUNT(*)subquery in the given example isn't that big an overhead.
但是那个子查询是重新运行的主要查询。我在这里展示了这个问题的大部分的一个小例子，尽管我知道COUNT(*)给定例子中的附加子查询并不是那么大的开销。
Edit: I do not know why the question is downvoted. The COUNT(*)I'm trying to get is from a view (a temporary table) in the query which is a large query with 5 to 6 joins and additional where clauses. To re-run the query as a subquery to get the count is inefficient, and I can see the bottleneck as well.
编辑：我不知道为什么这个问题被否决了。在COUNT(*)我想要得到的是从查询的图（临时表），这是5至6联接和额外的where子句大型查询。将查询作为子查询重新运行以获取计数是低效的，我也可以看到瓶颈。
Here is the actual query:
这是实际查询：
SELECT U.UserName, E.Title, AE.Mode, AE.AttemptNo,
IF(AE.Completed = 1, 'Completed', 'Incomplete'),
(
SELECT COUNT(DISTINCT(FK_QId))
FROM attempt_question AS AQ
WHERE FK_ExcAttemptId = @excAttemptId
) AS Inst_Count,
(
SELECT COUNT(DISTINCT(AQ.FK_QId))
FROM attempt_question AS AQ
JOIN `question` AS Q
ON Q.PK_Id = AQ.FK_QId
LEFT JOIN actions AS A
ON A.FK_QId = AQ.FK_QId
WHERE AQ.FK_ExcAttemptId = @excAttemptId
AND (
Q.Type = @descQtn
OR Q.Type = @actQtn
AND A.type = 'CTVI.NotImplemented'
AND A.IsDelete = @status
AND (
SELECT COUNT(*)
FROM actions
WHERE FK_QId = A.FK_QId
AND type != 'CTVI.NotImplemented'
AND IsDelete = @status
) = 0
)
) AS NotEvalInst_Count,
(
SELECT COUNT(DISTINCT(FK_QId))
FROM attempt_question AS AQ
WHERE FK_ExcAttemptId = @excAttemptId
AND Mark = @mark
) AS CorrectAns_Count,
E.AllottedTime, AE.TimeTaken
FROM attempt_exercise AS AE
JOIN ctvi_exercise_tblexercise AS E
ON AE.FK_EId = E.PK_EId
JOIN ctvi_user_table AS U
ON AE.FK_UId = U.PK_Id
JOIN ctvi_grade AS G
ON AE.FK_GId = G.PK_GId
WHERE AE.PK_Id = @excAttemptId
-- AND COUNT(AE.*) = @number --the portion in contention.
Kindly ignore the above query and guide me to right direction from the small example query I posted, thanks.
请忽略上述查询，并从我发布的小示例查询中引导我走向正确的方向，谢谢。
采纳答案by ypercube??
In MySQL, you can only do what you tried:
在 MySQL 中，你只能做你尝试过的事情：
SELECT id, fruit, pip
FROM plant
WHERE (
SELECT COUNT(*)
FROM plant
) = 2;
or this variation:
或这种变化：
SELECT id, fruit, pip
FROM plant
JOIN
(
SELECT COUNT(*) AS cnt
FROM plant
) AS c
ON c.cnt = 2;
Whether the 1st or the 2nd is more efficient, depends on the version of MySQL (and the optimizer). I would bet on the 2nd one, on most versions.
第一个还是第二个更有效，取决于 MySQL 的版本（和优化器）。在大多数版本中，我会打赌第二个。
In other DBMSs, that have window functions, you can also do the first query that @Andomar suggests.
在其他具有窗口函数的 DBMS 中，您也可以执行@Andomar 建议的第一个查询。
Here is a suggestion to avoid the bottleneck of calculating the derived table twice, once to get the rows and once more to get the count. If the derived table is expensive to be calculated, and its rows are thousands or millions, calculating them twice only to throw them away, is a problem, indeed. This may improve efficiency as it will limit the intermediately (twice) calculated rows to 3:
这里有一个建议，以避免两次计算派生表的瓶颈，一次是获取行数，一次是获取计数。如果派生表的计算成本很高，并且它的行数是数千或数百万，那么计算两次却将它们扔掉，确实是一个问题。这可能会提高效率，因为它将中间（两次）计算的行限制为 3：
SELECT p.*
FROM
( SELECT id, fruit, pip
FROM plant
LIMIT 3
) AS p
JOIN
( SELECT COUNT(*) AS cnt
FROM
( SELECT 1
FROM plant
LIMIT 3
) AS tmp
) AS c
ON c.cnt = 2 ;
回答by Andomar
After re-reading your question, you're trying to return rows only if there are 2 rows in the entire table. In that case I think your own example query is already the best.
重新阅读您的问题后，您仅在整个表中有 2 行时才尝试返回行。在这种情况下，我认为您自己的示例查询已经是最好的。
On another DBMS, you could use a Windowing function:
在另一个 DBMS 上，您可以使用 Windowing 函数：
select *
from (
select *
, count(*) over () as cnt
from plant
) as SubQueryAlias
where cnt = 2
But the overclause is not supported on MySQL.
但是overMySQL 不支持该子句。
old wrong anser below
下面的旧错误分析仪
The whereclause works before grouping. It works on single rows, not groups of rows, so you can't use aggregates like countor maxin the whereclause.
该where子句在分组之前起作用。它适用于单行，而不是行组，因此您不能在子句中使用count或使用聚合。maxwhere
To set filters that work on groups of rows, use the havingclause. It works after grouping and can be used to filter with aggregates:
要设置对行组起作用的过滤器，请使用having子句。它在分组后工作，可用于使用聚合进行过滤：
SELECT id, fruit, pip
FROM plant
GROUP BY
id, fruit, pip
HAVING COUNT(*) = 2;
回答by Stack Man
The other answers do not fulfill the original question which was to filter the results "without using a subquery".
其他答案不满足原始问题，即“不使用子查询”过滤结果。
You can actually do this by using a variable in 2 consecutive MySQL statements:
您实际上可以通过在 2 个连续的 MySQL 语句中使用一个变量来做到这一点：
SET @count=0;
SELECT * FROM
(
SELECT id, fruit, pip, @count:=@count+1 AS count
FROM plant
WHERE
) tmp
WHERE @count = 2;
