合并所有两个表，但列的差异数 [英] union all two table but diff number of column

问题描述

select count(*) as total FROM ( SELECT * FROM database1.订购WHERE号，如"11111111111111111" UNION ALL SELECT * FROM database2.订购WHERE号，如"11111111111111111")AS b

select count(*) as total FROM ( SELECT * FROM database1.orders WHERE number LIKE "11111111111111111" UNION ALL SELECT * FROM database2.orders WHERE number LIKE "11111111111111111" ) AS b

但是我遇到了错误:

使用的SELECT语句具有不同的列数

The used SELECT statements have a different number of columns

因为运行 SELECT * FROM database2.订单的编号类似于"11111111111111111" ，结果为空.

because run SELECT * FROM database2.orders WHERE number LIKE "11111111111111111" give me a result is null.

如何将其与查询合并，因为通过查询可以帮助我处理分页

How to merge it with a query because with a query to help me process the pagination

感谢帮助！

推荐答案

只需在union all之前进行聚合:

select sum(cnt) as total
FROM ((SELECT count(*) as cnt
       FROM database1.orders
       WHERE number LIKE '11111111111111111'
      )
      UNION ALL
      (SELECT count(*) as cnt
       FROM database2.orders
       WHERE number LIKE '11111111111111111'
      )
     ) t;

请注意，我将字符串定界符更改为单引号而不是双引号.优良作法是对字符串和日期常量(不包括其他任何内容)使用单引号.

Note I changed the string delimiter to be a single quote rather than a double quote. It is good practice to use single quotes for string and date constants (and nothing else).

顺便说一句，您也可以使用联接来做到这一点:

By the way, you can also do this using a join:

select o1.cnt1, o2.cnt1, (o1.cnt1 + o2.cnt1) as total
FROM (SELECT count(*) as cnt1
      FROM database1.orders
      WHERE number LIKE '11111111111111111'
     ) o1 cross join
     (SELECT count(*) as cnt2
      FROM database2.orders
      WHERE number LIKE '11111111111111111'
     ) o2;

这使得获取两个数据库的单个计数变得更加容易.

This makes it easier to get the individual counts for the two databases.

这篇关于合并所有两个表，但列的差异数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！