mysql - 简单的 MySQL View 由来自不同表的聚合数据组成

Question

尝试创建一个简单的 View ，将总计放在一行中。这里的技巧是我想从多个表中获取这些总计。

这样我就可以选择这一行来获取我想要跟踪的一堆内容的总计。

到目前为止，我还无法让它发挥作用，并且大多数关于此类事情的帖子都与联接有关。

CREATE VIEW stats AS
  select COUNT(DISTINCT publisher.name) as totalPublisher,
  select COUNT(DISTINCT series.name) as totalSeries
 From
  publisher,
  series

Answer 1

您可以在SELECT中使用子查询:

CREATE VIEW stats AS
  select (select COUNT(DISTINCT p.name) from publisher p) as totalPublisher,
         (select COUNT(DISTINCT s.name) from series s) as totalSeries;

注意:表中的名称很可能不同。如果是这样，这样做会更有效:

CREATE VIEW stats AS
  select (select count(*) from publisher p) as totalPublisher,
         (select count(*) from series s) as totalSeries;