mysql - 如何在不编写与子查询相同的查询的情况下在 WHERE 子句中使用实际行数 (COUNT(*))？

Question

我有这样的东西:

SELECT id, fruit, pip 
FROM   plant 
WHERE  COUNT(*) = 2;

我想这个奇怪的查询是不言自明的。 COUNT(*) 表示plant 表中的行数。 我的要求是，仅当表中的总行数 = 2 时，我才需要从指定字段中检索值。这不起作用，但是:聚合函数 COUNT 的使用无效 .

我不能这样做:

SELECT COUNT(*) as cnt, id, fruit, pip 
FROM   plant 
WHERE  cnt = 2;

一方面，它将输出的行数限制为 1，另一方面，它给出了相同的错误:聚合函数的无效使用。

我能做的是:

SELECT id, fruit, pip 
FROM   plant 
WHERE  (
        SELECT COUNT(*) 
        FROM   plant
       ) = 2;

但是那个子查询是主查询重新运行的。我在这里展示的是问题的较大部分的一个小示例，尽管我知道给定示例中的附加 COUNT(*) 子查询不是那么大的开销。

编辑:我不知道为什么这个问题被否决了。 COUNT(*) 我试图从查询中的一个 View (一个临时表)中获取，该查询是一个具有 5 到 6 个连接和附加 where 子句的大型查询。将查询作为子查询重新运行以获取计数是低效的，我也可以看到瓶颈。

这是实际的查询:

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.

请忽略上述查询并根据我发布的小示例查询引导我正确的方向，谢谢。

Answer 1

在 MySQL 中，您只能做您尝试过的事情:

SELECT id, fruit, pip 
FROM   plant 
WHERE  (
        SELECT COUNT(*) 
        FROM   plant
       ) = 2;

或者这个变体:

SELECT id, fruit, pip 
FROM   plant 
  JOIN
      (
        SELECT COUNT(*) AS cnt 
        FROM   plant
       ) AS c
    ON c.cnt = 2;

第一种还是第二种更有效，取决于 MySQL 的版本(和优化器)。我敢打赌第二个，在大多数版本上。

在其他具有窗口函数的 DBMS 中，您也可以执行@Andomar 建议的第一个查询。

---

这里有一个建议，可以避免两次计算派生表的瓶颈，一次获取行，再一次获取计数。如果派生表的计算成本很高，它的行有几千或几百万，计算两次就扔掉，确实是个问题。这可能会提高效率，因为它将中间(两次)计算的行限制为 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 ;