如何在Oracle SQL Developer中计算两张表的计数差值？

基础总行数差值计算 #

直接分别统计两张表的总行数，再做减法即可，用Oracle的虚表dual返回单行结果：

SELECT 
    (SELECT COUNT(*) FROM table_a) - (SELECT COUNT(*) FROM table_b) AS row_count_difference
FROM dual;

• 结果为正表示table_a行数多于table_b，为负则相反。
• 如果需要统计符合特定条件的行数差，在子查询中添加WHERE过滤：

  SELECT 
      (SELECT COUNT(*) FROM table_a WHERE status = 'ACTIVE') - 
      (SELECT COUNT(*) FROM table_b WHERE status = 'ACTIVE') AS active_row_difference
  FROM dual;
  

按分组字段计算差值 #

如果两张表有共同的分组字段（比如category），需要按分组统计各分组的计数差，可以用全连接实现：

SELECT 
    COALESCE(a.category, b.category) AS category,
    NVL(a.count_a, 0) - NVL(b.count_b, 0) AS count_difference
FROM 
    (SELECT category, COUNT(*) AS count_a FROM table_a GROUP BY category) a
FULL OUTER JOIN 
    (SELECT category, COUNT(*) AS count_b FROM table_b GROUP BY category) b
ON a.category = b.category;

• COALESCE用于获取非空的分组字段，确保包含两张表的所有分组。
• NVL将空值转为0，避免因某张表无对应分组导致的计算错误。

内容的提问来源于stack exchange，提问作者DanT96