sql三重联接：计数上不明确的属性名称

Question

所以我想计算书籍的数量，但是这些书籍存储在两个具有相同属性名称的不同表中。 我想要得到一个看起来像这样的结果：

  name1 [total number of books of 1]
  name2 [total number of books of 2]    

我尝试了这个三重连接；

SELECT DISTINCT name, count(book)
FROM writes w
LEFT JOIN person p on p.id = w.author
LEFT JOIN book b on b.title = w.book
LEFT JOIN controls l on l.controller=p.id
GROUP BY name
ORDER BY name DESC

但由于 book 作为写入和控件中的属性存在，因此它无法执行查询。 只有当我省略其中一个连接以便它可以识别书籍时，它才能做到这一点。

我怎样才能告诉sql引擎计算每个人的两个书籍属性的数量？

Answer 1

作为您感兴趣的数据库设计的结果，您应该发出 2 个不同的 sql，然后合并它们以处理单个输出。

A)

SELECT DISTINCT w.name as 'Name', count(w.book) as 'Cnt'
FROM writes w
LEFT JOIN person p on p.id = w.author
LEFT JOIN book b on b.title = w.book

B)

SELECT DISTINCT l.name as 'Name', count(l.book) as 'Cnt'
FROM controls l
LEFT JOIN person p on p.id = l.controller
LEFT JOIN book b on b.title = l.book

出于您的目的，您可以获取 A 和 B 的 UNION。

或者您可以将它们用作第三条 SQL 上的数据源

select A.Name, sum(A.Cnt+B.Cnt) 
from  A,  B 
where A.Name = B.Name
group by A.Name
order by A.Name

Answer 2

WITH T AS 
( 
  SELECT DISTINCT 'WRITES' FROMTABLE, w.name, w.count(book) 
    FROM writes w
    LEFT JOIN person p on p.id = w.author
    LEFT JOIN book b on b.title = w.book
  GROUP BY name
  UNION ALL 
  SELECT DISTINCT 'CONTROLLS' FROMTABLE, c.name, count(c.book) 
    FROM controlls c
    LEFT JOIN person p on p.id = c.author
    LEFT JOIN book b on b.title = c.book
  GROUP BY name
)
SELECT * FROM T ORDER BY NAME

应该有效。

华泰

Answer 3

这将根据每个不同作者的 ID 来确定他们写了多少本书。预聚合将返回每个作者的一条记录以及该作者的书籍数量。然后，加入 person 表以获取姓名。我将其保留为作者的 ID 和姓名的原因是...如果您有两个作者“John Smith”，但他们各自的 ID 为 123 和 389，您不会希望这些汇总到相同的值，该怎么办？人（或者你）。

select
      P.ID,
      P.Name,
      PreAgg.BooksPerAuthor
   from
      ( select
              w.author,
              count(*) BooksPerAuthor
           from
              writes w
           group by
              w.author ) PreAgg
      JOIN Person P
         on PreAgg.Author = P.id
   order by 
      P.Name