如何编写SQL查询从品牌客户表生成指定格式的共同客群统计结果表

请基于下方给出的Brand表，生成符合要求格式的输出表。

Brand表 #

目标输出表 #

输出字段说明 #

No_Common_Customers列表示同时访问过Brand1和Brand2的客户总数量；C1、C2列分别代表同时访问过两个品牌的第一位、第二位客户名称，若仅存在1位共同客户，则仅在其中一列展示客户名，另一列为NULL。

现有错误代码 #

用户编写的代码运行结果不符合要求，代码如下：

select b1.Brand Brand1, b2.Brand Brand2, b1.Customer c1, b2.Customer c2
from brand b1 join
brand b2
where b1.Brand <> b2.Brand
group by Brand1, Brand2;

错误输出结果 #

实现思路 #

1. 生成不重复品牌对：通过b1.Brand < b2.Brand避免出现(A,B)和(B,A)这类重复配对，和目标输出的品牌顺序完全匹配
2. 匹配共同客户：关联条件增加b1.Customer = b2.Customer，筛选出同时访问两个品牌的用户
3. 排序取数：对每个品牌对下的共同客户排序后，分别取出第一位、第二位客户，同时统计总数量

参考代码 #

支持窗口函数的数据库（MySQL8.0+/PostgreSQL等通用版本） #

WITH common_cust AS (
    SELECT 
        b1.Brand AS Brand1,
        b2.Brand AS Brand2,
        b1.Customer AS cust,
        ROW_NUMBER() OVER (PARTITION BY b1.Brand, b2.Brand ORDER BY b1.Customer) AS rn
    FROM brand b1
    INNER JOIN brand b2
        ON b1.Brand < b2.Brand
        AND b1.Customer = b2.Customer
)
SELECT 
    Brand1,
    Brand2,
    MAX(CASE WHEN rn = 1 THEN cust END) AS C1,
    MAX(CASE WHEN rn = 2 THEN cust END) AS C2,
    COUNT(*) AS No_Common_Customers
FROM common_cust
GROUP BY Brand1, Brand2
ORDER BY Brand1, Brand2;

低版本MySQL兼容版本（无窗口函数） #

SELECT 
    b1.Brand AS Brand1,
    b2.Brand AS Brand2,
    MIN(b1.Customer) AS C1,
    CASE WHEN COUNT(DISTINCT b1.Customer) >=2 THEN MAX(b1.Customer) ELSE NULL END AS C2,
    COUNT(DISTINCT b1.Customer) AS No_Common_Customers
FROM brand b1
INNER JOIN brand b2
    ON b1.Brand < b2.Brand
    AND b1.Customer = b2.Customer
GROUP BY b1.Brand, b2.Brand
ORDER BY b1.Brand, b2.Brand;

内容的提问来源于stack exchange，提问作者Tushar Singh