mysql - 计算单独表中 2 列的并集

Question

我正在尝试编写一个 sql 查询来计算驻留在由 UNION ALL 连接在一起的不同表中的两列的行数。

SELECT SUM(usernames) AS total 
   FROM 
   ((SELECT count(username) AS usernames 
        FROM table1 
        WHERE columa < '20' AND columnb = 'c' ) 
   UNION ALL 
   (SELECT count(name) AS usernames 
       FROM table2 
       WHERE columna2 < '20' and columnb2 = 'Cat' ))

这当然行不通。我在 phpMyAdmin 中运行了这条语句，它给了我错误....

Every derived table must have it's own alias.

于是将SQL语句重写为...

SELECT SUM(usernames) AS total 
   FROM 
   ((SELECT count(username) AS usernames 
        FROM table1 a 
        WHERE a.columa < '20' AND a.columnb = 'c' ) 
   UNION ALL 
   (SELECT count(name) AS usernames 
       FROM table2 b 
       WHERE b.columna2 < '20' and b.columnb2 = 'Cat' ))

这也给了我同样的错误...

Every derived table must have it's own alias.

我在这里错过了什么？

Answer 1

select 语句缺少别名，特别是 table1 和 table2。尝试删除括号:

SELECT SUM(usernames) AS total 
   FROM 
   (SELECT count(username) AS usernames 
        FROM table1 
        WHERE columa < '20' AND columnb = 'c'  
   UNION 
   SELECT count(name) AS usernames 
       FROM table2 
       WHERE columna2 < '20' and columnb2 = 'Cat' ) a

或者创建别名:

SELECT SUM(usernames) AS total 
   FROM 
   ((SELECT count(username) AS usernames 
        FROM table1 
        WHERE columa < '20' AND columnb = 'c' )  a
   UNION ALL 
   (SELECT count(name) AS usernames 
       FROM table2 
       WHERE columna2 < '20' and columnb2 = 'Cat' ) b) c