aaa

表结构假设

假设表1、表2和表3的结构如下：
表1 (客户表): customer
id (客户ID)
code (邀请码)
表2 (邀请码表): code_agent
code (邀请码)
agent (人员)
表3 (客户额度表): customer_quota
id (客户ID)
quota (额度)
1. 整合同一人员的邀请码（按使用次数大到小取唯一），并修改到表一
步骤1：找到每个人员最常用的邀请码

WITH CodeUsage AS (
    SELECT 
        agent,
        code,
        COUNT(*) AS usage_count
    FROM 
        code_agent
    GROUP BY 
        agent, code
),
MostUsedCode AS (
    SELECT 
        agent,
        code,
        usage_count,
        ROW_NUMBER() OVER (PARTITION BY agent ORDER BY usage_count DESC) AS rn
    FROM 
        CodeUsage
)
SELECT 
    agent,
    code
FROM 
    MostUsedCode
WHERE 
    rn = 1;

步骤2：更新表1中的邀请码

WITH CodeUsage AS (
    SELECT 
        agent,
        code,
        COUNT(*) AS usage_count
    FROM 
        code_agent
    GROUP BY 
        agent, code
),
MostUsedCode AS (
    SELECT 
        agent,
        code,
        usage_count,
        ROW_NUMBER() OVER (PARTITION BY agent ORDER BY usage_count DESC) AS rn
    FROM 
        CodeUsage
)
UPDATE customer c
SET c.code = (
    SELECT m.code
    FROM MostUsedCode m
    JOIN code_agent ca ON ca.agent = m.agent AND ca.code = c.code
    WHERE m.rn = 1
);

2. 查看客户被多次营销的情况，并返回交叉客户最多的人员
步骤1：找到被多次营销的客户

WITH CustomerMarketing AS (
    SELECT 
        c.id AS customer_id,
        ca.agent,
        COUNT(*) AS marketing_count
    FROM 
        customer c
    JOIN 
        code_agent ca
    ON 
        c.code = ca.code
    GROUP BY 
        c.id, ca.agent
)
SELECT 
    customer_id,
    agent,
    marketing_count
FROM 
    CustomerMarketing
WHERE 
    marketing_count > 1;

步骤2：返回交叉客户最多的人员

WITH CustomerMarketing AS (
    SELECT 
        c.id AS customer_id,
        ca.agent,
        COUNT(*) AS marketing_count
    FROM 
        customer c
    JOIN 
        code_agent ca
    ON 
        c.code = ca.code
    GROUP BY 
        c.id, ca.agent
),
MultipleMarketing AS (
    SELECT 
        customer_id,
        agent,
        marketing_count
    FROM 
        CustomerMarketing
    WHERE 
        marketing_count > 1
),
AgentCustomerCount AS (
    SELECT 
        agent,
        COUNT(DISTINCT customer_id) AS customer_count
    FROM 
        MultipleMarketing
    GROUP BY 
        agent
)
SELECT 
    agent,
    customer_count
FROM 
    AgentCustomerCount
ORDER BY 
    customer_count DESC
LIMIT 1;

解释
1. 整合同一人员的邀请码：
      使用 WITH 子句计算每个人员的邀请码使用次数。
      使用 ROW_NUMBER() 函数按使用次数降序排列，取每个人员最常用的邀请码。
      更新表1中的邀请码。
2. 查看客户被多次营销的情况：
      使用 WITH 子句计算每个客户的营销次数。
      过滤出被多次营销的客户。
      统计每个人员被多次营销的客户数量。
     返回交叉客户最多的人员。