Sql计数(*)来自多个表，但应该出现交集

Question

Select distinct(job_id) from A where condition
Select distinct(job_id) from B where condition
Select distinct(job_id) from C where condition

这里可能有1,2和3有一些共同的jobId我需要的是所有job_id的计数，它可能不会重复。

Answer 1

是否使用UNION？

Select job_id from A where condition
UNION
Select job_id from B where condition
UNION
Select job_id from C where condition

Answer 2

我相信EXCEPT就是您可能在这里寻找的，它在多个查询中提供了一个不相交的结果集。这可能也可以用JOIN逻辑来处理，但由于问题中的细节有限，这有点难以判断。

这是关于EXCEPT关键字的Postgres文档。

WITH data AS
(
SELECT job_id FROM A WHERE condition
EXCEPT
SELECT job_id FROM B WHERE condition
EXCEPT
SELECT job_id FROM C WHERE condition
)
SELECT COUNT(*) FROM data;

---

这是一个有效但不可扩展的解决方案。我很犹豫是否发布它，因为我觉得它很难看，肯定有更好的方法，但也许这会给你指明正确的方向。

WITH a_b_dups AS
(
SELECT job_id FROM a
INTERSECT
SELECT job_id FROM b
), b_c_dups AS
(
SELECT job_id FROM b
INTERSECT
SELECT job_id FROM c
), a_c_dups AS
(
SELECT job_id FROM a
INTERSECT
SELECT job_id FROM c
), dups AS
(
SELECT job_id FROM a_b_dups
UNION
SELECT job_id FROM b_c_dups
UNION
SELECT job_id FROM a_c_dups
), uniques AS
(
SELECT job_id FROM a
UNION
SELECT job_id FROM b
UNION
SELECT job_id FROM c
EXCEPT
SELECT job_id FROM dups
)
SELECT COUNT(*) FROM uniques;

DBFiddle显示一个工作示例。