独立表中2列的并集计数

t1qtbnec 于 2021-06-23 发布在 Mysql

关注(0)|答案(3)|浏览(295)

我正在尝试编写一个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中运行了这个语句，它给了我一个错误。。。。
每个派生表都必须有自己的别名。
因此，将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' ))

这也给了我同样的错误。。。
每个派生表都必须有自己的别名。
我错过了什么？

sql mysql

来源：https://stackoverflow.com/questions/50594170/count-union-of-2-columns-in-seperate-tables

3条答案

按热度按时间

krcsximq1#

问题不在于括号——这些括号在大多数数据库中都很好。您只需在最后一个别名之后添加一个别名：

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'
      )
     ) t
-------^ voila!

这看起来有点混乱，但是 union all 不要取别名。在mysql中，子查询的行为类似于 from 条款。

赞(0）回复(0）举报 2021-06-23

ix0qys7i2#

SELECT *
  FROM 
     ( SELECT 1 n) -- this a derived table, with a column alias (n)
     x -- and this is a table alias for said table

赞(0）回复(0）举报 2021-06-23

iklwldmw3#

select语句缺少别名，而不是表1和表2。尝试删除括号：

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

赞(0）回复(0）举报 2021-06-23

我来回答

独立表中2列的并集计数

3条答案

相关问题

热门标签

最新问答