实现无重复的product_features与feature_text高效关联及多语言列转换

核心需求 #

• 返回所有product_features记录，无重复行
• 优先匹配产品特定的特性文本，无特定文本则使用通用文本
• 将两种语言文本拆分为独立列（示例中为英文en_desc、中文zh_desc）
• 保证查询执行效率

表结构定义 #

-- 产品特性关联表：存储产品与特性代码的绑定关系
CREATE TABLE product_features (
    product_code VARCHAR(20) NOT NULL,
    feature_code VARCHAR(20) NOT NULL,
    PRIMARY KEY (product_code, feature_code) -- 复合主键避免重复绑定
);

-- 特性多语言文本表：product_code为NULL时表示通用文本
CREATE TABLE feature_text (
    product_code VARCHAR(20),
    feature_code VARCHAR(20) NOT NULL,
    lang_code VARCHAR(5) NOT NULL,
    feature_desc TEXT NOT NULL,
    PRIMARY KEY (product_code, feature_code, lang_code) -- 复合主键保证唯一文本
);

测试数据 #

-- 插入产品特性数据
INSERT INTO product_features (product_code, feature_code) VALUES
('B12XBE', 'DSP'),
('UTB2EF', 'DSP'),
('X9Y8Z7', 'DSP'),
('UTB2EF', 'XAB'),
('X9Y8Z7', 'XAB');

-- 插入特性文本数据（含产品特定+通用文本）
INSERT INTO feature_text (product_code, feature_code, lang_code, feature_desc) VALUES
-- B12XBE的DSP专属文本
('B12XBE', 'DSP', 'en', 'Custom DSP for B12XBE'),
('B12XBE', 'DSP', 'zh', 'B12XBE专属DSP'),
-- 通用特性文本
(NULL, 'DSP', 'en', 'General Digital Signal Processor'),
(NULL, 'DSP', 'zh', '通用数字信号处理器'),
(NULL, 'XAB', 'en', 'General Extended Access Block'),
(NULL, 'XAB', 'zh', '通用扩展访问模块');

原查询问题分析 #

常见错误查询（如使用LEFT JOIN同时匹配特定+通用文本再GROUP BY）会产生中间重复行，且JOIN条件未做优先过滤，导致效率低下：

-- 存在问题的原查询示例
WITH feature_text_combined AS (
    SELECT 
        pf.product_code,
        pf.feature_code,
        ft.lang_code,
        ft.feature_desc
    FROM product_features pf
    LEFT JOIN feature_text ft 
        ON pf.feature_code = ft.feature_code
        AND (ft.product_code = pf.product_code OR ft.product_code IS NULL)
)
SELECT 
    product_code,
    feature_code,
    MAX(CASE WHEN lang_code = 'en' THEN feature_desc END) AS en_desc,
    MAX(CASE WHEN lang_code = 'zh' THEN feature_desc END) AS zh_desc
FROM feature_text_combined
GROUP BY product_code, feature_code;

问题点：同一特性会同时匹配特定文本和通用文本，生成重复中间行；GROUP BY阶段需要额外去重计算，且JOIN未利用索引过滤，效率较低。

修正后的高效查询 #

SELECT 
    pf.product_code,
    pf.feature_code,
    -- 优先取产品特定文本，无则 fallback 到通用文本
    COALESCE(ft_specific.en_desc, ft_general.en_desc) AS en_desc,
    COALESCE(ft_specific.zh_desc, ft_general.zh_desc) AS zh_desc
FROM product_features pf
-- 左连接产品特定的多语言文本（预先转成列）
LEFT JOIN (
    SELECT 
        product_code,
        feature_code,
        MAX(CASE WHEN lang_code = 'en' THEN feature_desc END) AS en_desc,
        MAX(CASE WHEN lang_code = 'zh' THEN feature_desc END) AS zh_desc
    FROM feature_text
    WHERE product_code IS NOT NULL
    GROUP BY product_code, feature_code
) ft_specific ON pf.product_code = ft_specific.product_code 
             AND pf.feature_code = ft_specific.feature_code
-- 左连接通用多语言文本（预先转成列）
LEFT JOIN (
    SELECT 
        feature_code,
        MAX(CASE WHEN lang_code = 'en' THEN feature_desc END) AS en_desc,
        MAX(CASE WHEN lang_code = 'zh' THEN feature_desc END) AS zh_desc
    FROM feature_text
    WHERE product_code IS NULL
    GROUP BY feature_code
) ft_general ON pf.feature_code = ft_general.feature_code;

查询优化说明 #

1. 避免重复行：预先将feature_text按「产品+特性」「特性」分组并转成多语言列，JOIN时不会产生重复数据
2. 逻辑清晰：用COALESCE直接实现「特定文本优先，通用文本兜底」的逻辑
3. 高效过滤：子查询提前过滤特定/通用文本，减少JOIN的数据量；结合feature_text的复合主键索引，子查询可直接命中索引，大幅提升查询速度
4. 扩展性强：新增语言时仅需在子查询的CASE语句中添加对应lang_code分支即可

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