比较两个不同 SQL 表的计数

Question

>我有两个表 A 和 B，正在尝试比较它们的计数，例如

SELECT COUNT(*) FROM table_a WHERE day = '2018-09-27' AS A,
COUNT(*) FROM table_b WHERE day = '2018-09-27' AS B;

当我这样做作为中间步骤时，我收到一个错误。 我的目标是将输出作为

day      table_a    table_b    difference    %difference
9/25/18    1            1             0          0
9/24/18    2            1             1          50%

Answer 1

试试这个：

SELECT COALESCE(a.day, b.day) AS day
, count_a
, count_b
, count_a - count_b AS difference
FROM ( SELECT day, count(*) AS count_a FROM table_a GROUP BY day ) a
FULL JOIN ( SELECT day, count(*) AS count_b FROM table_b GROUP BY day ) b
ON b.day = a.day

Answer 2

执行此操作的简单方法

select 
A,
B,
A-B as difference
From
(
select
(SELECT COUNT(*) FROM table_a WHERE day = '2018-09-27') as A,
(select COUNT(*) FROM table_b WHERE day = '2018-09-27') as B
) Res