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

你需要为你的列Col1指定一个唯一的别名，因为它在join中使用，它需要唯一的列来相互匹配。如果遇到重复列，它将引发错误1060。检查此：Duplicate column names in SQL query

在你的情况下，MySQL与表1和表2中的Col1混淆，因此错误。一旦你已经使用过，你可以SELECT COUNT(*) from YourQuery

Answer 2

环绕你整个查询括号（使它成为一个子查询），并从中做一个SELECT COUNT（*）：

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

Answer 3

整个包住查询作为

SELECT COUNT(*) FROM (<Your query>) 

Answer 4

真正使用mysql_num_rows。对于联盟来说，数据库无论如何都需要建立一个临时表，这是很昂贵的事情。即使可以直接在SQL中执行操作，也不会通过让MySQL给你那个数字来节省很多。

无论如何，当您只使用mysql_num_rows函数时，数据不会发送到PHP。

Answer 5

您需要为您的列Col1指定一个唯一的别名，因为它用于需要唯一列彼此匹配的join。如果遇到重复列，它将引发错误1060。检查此：Duplicate column names in SQL query

在你的情况下，MySQL与表1和表2中的Col1混淆，因此错误。一旦你使用了，你可以SELECT COUNT(*) from YourQuery