php - MySQL:如何计算复杂查询返回的行数

Question

这感觉应该很容易，但我就是做不到，我已经尝试了所有方法。

我怎样才能只是得到这个查询返回的行数:

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2' 
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
        AND Alias2.Col7 LIKE 'blah6' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

UNION 

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2' 
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
        AND Alias2.Col5 LIKE 'blah6' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

UNION 

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2'
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

我想我可以只使用 PHP 执行查询，然后对结果执行 mysql_num_rows，但我想直接使用 SQL 执行此操作，因为我听说它更快，因为它节省了一个步骤。

谢谢!

编辑:

以下是我的错误(#1060 - 重复的列名称“Col1”):

   SELECT COUNT(*) FROM (
   (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col7 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) 

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col5 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) 

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2'
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50)
   ) a

以下是我的错误(#1064 - 你的 SQL 语法有错误；):

 SELECT COUNT(*) FROM (
   (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col7 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) a

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col5 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) b

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2'
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) c
   ) z

Answer 1

将整个查询包裹在括号中(使其成为子查询)并从中执行选择计数(*):

select count(*) from (YOUR HUGE QUERY UNION YOUR HUGE QUERY) a