计算多个表中的公共用户数。

Question

我有4张表如下所示

​

​

我基本上想知道表2、3和4中有多少来自table1的用户。类似地，对于table2，我想知道表1、3和4中有多少用户。

基本上所有可能的组合。我想要的最终结果如下

​

​

我试图解决的方法之一是使用其他表执行left-join of table1，然后是count，以获得输出的第一行。但是，对于所有可能的组合来说，这样做都不是优化的。我在寻找其他可能的选择

我的代码也一样

SELECT 
COUNT(DISTINCT A.id) table1,
COUNT(DISTINCT B.id) table2,
COUNT(DISTINCT C.id) table3,
COUNT(DISTINCT D.id) table4
FROM table1 A
LEFT JOIN table2 B
ON A.id = B.id

LEFT JOIN table3 C
ON A.id = C.id

LEFT JOIN table4 D
ON A.id = D.id

大小提琴 (这个小提琴是针对mysql的，我正在寻找一种通用的基于SQL的方法，而不是任何特定于db的方法)

Answer 1

我建议：

with t as (
      select 'table1' as which, id from table1 union all
      select 'table2' as which, id from table2 union all
      select 'table3' as which, id from table3 union all
      select 'table4' as which, id from table4
     )
select ta.which,
       sum(case when tb.which = 'table1' then 1 else 0 end) as cnt_table1,
       sum(case when tb.which = 'table2' then 1 else 0 end) as cnt_table2,
       sum(case when tb.which = 'table3' then 1 else 0 end) as cnt_table3,
       sum(case when tb.which = 'table4' then 1 else 0 end) as cnt_table4
from t ta left join
     t tb
     on ta.id = tb.id
group by ta.which;

注意:这假设id在每个表中都是唯一的。给定列的名称和样本数据，这是一个合理的假设。但是，如果存在重复项，则可以将CTE中的union all更改为union。

这种结构也可以很容易地推广到其他表。

Answer 2

使用UNION ALL

演示

select 'table1' as col1,count(table1.id),count(table2.id),count(table3.id),count(table4.id) 
from table1
left join table2 on table1.id=table2.id
left join table3 on table1.id=table3.id
left join table4 on table1.id=table4.id
union all
select 'table2' ,count(table1.id),count(table2.id),count(table3.id),count(table4.id) 
from table2
left join table1 on table2.id=table1.id
left join table3 on table2.id=table3.id
left join table4 on table2.id=table4.id
union all
select 'table3' ,count(table1.id),count(table2.id),count(table3.id),count(table4.id) 
from table3
left join table1 on table3.id=table1.id
left join table2 on table3.id=table2.id
left join table4 on table3.id=table4.id
union all
select 'table4' ,count(table1.id),count(table2.id),count(table3.id),count(table4.id) 
from table4
left join table1 on table4.id=table1.id
left join table2 on table4.id=table2.id
left join table3 on table4.id=table3.id

产出：

col1    tbl1    tbl2    tbl3    tbl4
table1   8      3        2       2
table2   3      6        1       0
table3   2      1        5       0
table4   2      0        0       4