如何创建可展示3张不同表记录行数统计结果的SQL视图

方案1（最简洁，无需JOIN，适配绝大多数SQL数据库） #

直接将三个统计查询作为子查询放在SELECT字段列表中即可，每个子查询返回单行单列的统计值，拼接后自然形成你需要的一行三列结构：

CREATE VIEW table_count_view AS
SELECT
  (SELECT COUNT(*) FROM table1) AS count_a,
  (SELECT COUNT(*) FROM table2) AS count_b,
  (SELECT COUNT(*) FROM table3) AS count_c;

查询这个视图就可以直接得到你要的结果：

SELECT * FROM table_count_view;

输出和你期望的格式完全一致：

方案2（UNION ALL + 聚合兼容写法） #

如果部分数据库对上述子查询写法有兼容性问题，可以用UNION ALL先汇总三个统计值，再通过聚合函数转成单行：

CREATE VIEW table_count_view AS
SELECT
  MAX(CASE WHEN type = 'a' THEN cnt END) AS count_a,
  MAX(CASE WHEN type = 'b' THEN cnt END) AS count_b,
  MAX(CASE WHEN type = 'c' THEN cnt END) AS count_c
FROM (
  SELECT 'a' AS type, COUNT(*) AS cnt FROM table1
  UNION ALL
  SELECT 'b' AS type, COUNT(*) AS cnt FROM table2
  UNION ALL
  SELECT 'c' AS type, COUNT(*) AS cnt FROM table3
) t;

两种方案都不需要使用JOIN，也不需要额外处理表关联逻辑，性能和可读性都更好。

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