MySQL创建含客户最新合规评分的第四张表技术求助

要创建包含客户名称、标准名称及对应最新评分的表，核心是先筛选出每个客户-标准组合的最新评分记录，再关联客户和标准表获取名称，最终生成新表。

完整SQL语句 #

CREATE TABLE customer_latest_standard_compliance AS
SELECT
    c.customer_name,
    s.standard_name,
    psc.standard_compliance AS latest_compliance_score,
    psc.created_time AS latest_score_time
FROM
    partial_standard_compliance psc
INNER JOIN (
    -- 获取每个客户-标准的最新评分时间
    SELECT
        customer,
        standard,
        MAX(created_time) AS latest_time
    FROM
        partial_standard_compliance
    GROUP BY
        customer,
        standard
) latest_records ON psc.customer = latest_records.customer 
                 AND psc.standard = latest_records.standard 
                 AND psc.created_time = latest_records.latest_time
INNER JOIN customers c ON psc.customer = c.cid
INNER JOIN standards s ON psc.standard = s.sid;

步骤说明 #

• 子查询latest_records：通过GROUP BY customer, standard分组，用MAX(created_time)锁定每个客户对同一标准的最新评分时间，这是筛选最新记录的核心逻辑。
• 关联原评分表：将子查询结果与partial_standard_compliance关联，匹配客户ID、标准ID和最新时间，精准获取对应的最新评分值。
• 关联基础信息表：通过外键关联customers和standards表，将ID字段替换为易读的名称字段。
• 生成新表：借助CREATE TABLE ... AS SELECT语法，直接将查询结果转化为新表。

可选优化 #

如果需要给新表添加主键或索引，可在创建后执行：

ALTER TABLE customer_latest_standard_compliance
ADD PRIMARY KEY (customer_name, standard_name); -- 也可根据需求选用ID字段作为主键

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