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

Question

我有一个类似的查询

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 的限制。

那么我如何检索子查询返回的行数而不需要单独执行它？

Answer 1

您可以在 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 没有那么聪明。