sql - 根据单一标准从两个表中计数

Question

我正在尝试做这样的事情:

SELECT COUNT(topic.topic_id) + COUNT(post.post_id)
FROM topic, post WHERE author_id = ?

两个表都有 author_id 列。

我收到 column reference "author_id"is ambiguous 错误。

我如何告诉它 author_id 出现在两个表中？

Answer 1

虽然您可以，但您很可能不想连接两个表，因为这可能会导致不同的计数。此相关答案中的解释:
Two SQL LEFT JOINS produce incorrect result

两个子查询最快:

SELECT (SELECT COUNT(topic_id) FROM topic WHERE author_id = ?)
     + (SELECT COUNT(post_id)  FROM post  WHERE author_id = ?) AS result

如果topic_id和post_id在各自的表中都定义了NOT NULL，可以稍微简化一下:

SELECT (SELECT COUNT(<b>*</b>) FROM topic WHERE author_id = ?)
     + (SELECT COUNT(<b>*</b>) FROM post  WHERE author_id = ?) AS result

如果两个author_id 列中至少有一个唯一，则JOIN 在这种情况下也可以工作(但速度较慢，我不会使用它):

SELECT COUNT(t.topic_id) + COUNT(p.post_id) AS result
FROM   topic t
LEFT   post  p USING (author_id)
WHERE  t.author_id = ?;

如果您只想输入一次值，请使用 CTE :

WITH x AS (SELECT ? AS author_id)  -- enter value here
SELECT (SELECT COUNT(*) FROM topic JOIN x USING (author_id))
     + (SELECT COUNT(*) FROM post  JOIN x USING (author_id)) AS result

但一定要了解联接的工作原理。阅读有关 Joined Tables in the manual 的章节.