如何简化实体-属性-值（EAV）表的多次重复连接操作？

我使用EAV结构的custom_object表存储自定义数据元素，需要通过T_ID1、T_ID2、T_ID3三个字段关联record表，同时根据DESC字段的值获取对应记录的状态。目前我通过多次LEFT JOIN实现查询，但最多需要写30次此类连接，导致SQL脚本非常冗长。想确认当前实现是否为最优解，有没有更简洁的实现方式？比如临时存储过程或函数？

原SQL代码如下：

SELECT
    r.ID, 
    apples.status, 
    oranges.status, 
    bananas.status 
FROM
    record r
LEFT JOIN 
    custom_object apples ON apples.T_ID1 = r.T_ID1
                         AND apples.T_ID2 = r.T_ID2
                         AND apples.T_ID3 = r.T_ID3
                         AND apples.DESC = 'Apples'
LEFT JOIN 
    custom_object oranges ON oranges.T_ID1 = r.T_ID1
                          AND oranges.T_ID2 = r.T_ID2
                          AND oranges.T_ID3 = r.T_ID3
                          AND oranges.DESC = 'Oranges'
LEFT JOIN 
    custom_object bananas ON bananas.T_ID1 = r.T_ID1
                          AND bananas.T_ID2 = r.T_ID2
                          AND bananas.T_ID3 = r.T_ID3
                          AND bananas.DESC = 'Bananas'

优化方案：使用条件聚合替代多次LEFT JOIN #

你的当前实现不是最优解，多次LEFT JOIN会重复扫描custom_object表，不仅脚本冗长，还可能影响查询性能。更简洁高效的方式是条件聚合，只需要关联一次custom_object表，通过CASE WHEN配合聚合函数（如MAX）将不同DESC值的status列转行输出。

示例代码 #

SELECT
    r.ID,
    MAX(CASE WHEN co.DESC = 'Apples' THEN co.status END) AS apples_status,
    MAX(CASE WHEN co.DESC = 'Oranges' THEN co.status END) AS oranges_status,
    MAX(CASE WHEN co.DESC = 'Bananas' THEN co.status END) AS bananas_status
    -- 后续30个不同的DESC值，直接追加对应的CASE语句即可
FROM
    record r
LEFT JOIN 
    custom_object co ON co.T_ID1 = r.T_ID1
                     AND co.T_ID2 = r.T_ID2
                     AND co.T_ID3 = r.T_ID3
GROUP BY
    r.ID;

优势说明 #

• 脚本简洁：即使有30个不同的DESC值，只需追加30行CASE语句，无需重复编写关联逻辑
• 性能更优：仅扫描一次custom_object表，避免多次JOIN带来的重复扫描和数据冗余
• 扩展性强：新增DESC值时，仅需添加一行CASE语句即可

可选复用方案 #

如果这个查询需要频繁复用，可以将其封装为视图：

CREATE VIEW record_with_custom_status AS
SELECT
    r.ID,
    MAX(CASE WHEN co.DESC = 'Apples' THEN co.status END) AS apples_status,
    MAX(CASE WHEN co.DESC = 'Oranges' THEN co.status END) AS oranges_status,
    MAX(CASE WHEN co.DESC = 'Bananas' THEN co.status END) AS bananas_status
    -- 其他DESC对应的CASE语句
FROM
    record r
LEFT JOIN 
    custom_object co ON co.T_ID1 = r.T_ID1
                     AND co.T_ID2 = r.T_ID2
                     AND co.T_ID3 = r.T_ID3
GROUP BY
    r.ID;

之后查询直接使用SELECT * FROM record_with_custom_status即可。

如果需要动态指定DESC值，可以考虑创建存储过程，但对于固定的30个DESC值，条件聚合+视图的方案足够简洁高效。

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