mysql - 从子查询获取总行数？

我有一个查询

SELECT row1, row2
FROM (
    SELECT b.row21, COUNT(b.row21) AS hits
    FROM table2 AS b
    WHERE b.row22 = 'foo' OR b.row22 = 'bar'
    GROUP BY b.row21
    HAVING hits = 2
) AS b
INNER JOIN table1 AS a ON (b.row21 = a.row1)
WHERE row2 = 123
LIMIT 10

现在很明显，结果首先受附加的WHERE限制，最后受LIMIT限制。

那么，如何在不需要单独执行子查询的情况下检索子查询返回的行数呢？

最佳答案

您可以在MySQL中使用变量进行此操作：

SELECT row1, row2, @rn as numrows
FROM (
    SELECT b.row21, COUNT(b.row21) AS hits, @rn := @rn + 1
    FROM table2 b cross join
         (select @rn := 0) const
    WHERE b.row22 = 'foo' OR b.row22 = 'bar'
    GROUP BY b.row21
    HAVING hits = 2
) AS b
INNER JOIN table1 AS a ON (b.row21 = a.row1)
WHERE row2 = 123
LIMIT 10;

您冒着MySQL可能以完全不同的方式重写查询的风险，这可能避免生成某些行。一些SQL编译器足够聪明地执行此操作。我不认为MySQL这么聪明。

关于mysql - 从子查询获取总行数？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17854610/