这是一个postgresql示例。

假设这些是你的table

create table a (tstamp timestamp, weight int, destination text);
create table b (tstamp timestamp, weight int, destination text);

insert into a values
('2020-01-01 10:00:00', 40, 'germany'), ('2019-01-01 10:00:00', 50, 'germany'), 
('2020-04-01 10:00:00', 10, 'germany'), ('2019-04-01 10:00:00', 20, 'germany'), 
('2020-01-01 11:00:00', 40, 'congo'), ('2019-01-01 11:00:00', 50, 'congo'), 
('2020-04-01 11:00:00', 10, 'congo'), ('2019-04-01 12:00:00', 20, 'congo');

insert into b values
('2020-01-01 10:00:00', 40, 'germany'), ('2019-01-01 10:00:00', 50, 'germany'), 
('2020-04-01 11:00:00', 10, 'congo'), ('2019-04-01 11:00:00', 20, 'congo');

查询

我们从表a中取出一个关于源的记号。我们将它与表b中的同类信息结合起来。然后，我们使用 case 语句有选择地分别拉a和b的计数，也可以组合在一起。

with combined as (
  select count(*) as counter, 'a' as source from a
  where destination = 'germany' and date_part('hour', tstamp) = 10

  union all

  select count(*) as counter, 'b' as source from b
  where destination = 'germany' and date_part('hour', tstamp) = 10
)
select
  sum(case when source = 'a' then counter else 0 end) as count_a,
  sum(case when source = 'b' then counter else 0 end) as count_b,
  sum(counter) as counter_a_b
from combined

结果

count_a | count_b | counter_a_b
------: | ------: | ----------:
      4 |       2 |           6

例子：https://dbfiddle.uk/?rdbms=postgres_12&fiddle=100b4d5d4c94789d2f2c05d79c464166

sql server等效

with combined as (
  select count(*) as counter, 'a' as source from a
  where destination = 'germany' and datepart(hh, tstamp) = 10

  union all

  select count(*) as counter, 'b' as source from b
  where destination = 'germany' and datepart(hh, tstamp) = 10
)
select
  sum(case when source = 'a' then counter else 0 end) as count_a,
  sum(case when source = 'b' then counter else 0 end) as count_b,
  sum(counter) as counter_a_b
from combined

例子：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ff9dd4f864792437cb27d143106db186

mysql等效

select
  sum(case when source = 'a' then counter else 0 end) as count_a,
  sum(case when source = 'b' then counter else 0 end) as count_b,
  sum(counter) as counter_a_b
from (
  select count(*) as counter, 'a' as source from a
  where destination = 'germany' and hour(tstamp) = 10

  union all

  select count(*) as counter, 'b' as source from b
  where destination = 'germany' and hour(tstamp) = 10
) t

例子：https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=660b0c84c0c1b04141d19cc8c6b6af6d