单表查询正常，关联asset_attr_value表后SQL查询结果异常求助

核心问题分析 #

你遇到的结果数量不一致、出现无效数据的情况，主要源于以下几个原因：

• 一对多关联导致重复行：asset_attr里的单条资产记录，在asset_attr_value中可能对应多条属性值记录（比如一个资产有多个属性字段），内连接关联后，每条属性值都会与主表记录匹配，结果行数会比单表查询多。
• 内连接过滤无匹配记录：你用的是隐式内连接（逗号分隔两张表），如果某资产在asset_attr_value中没有对应属性值记录，这条资产会被直接过滤，导致结果行数比单表查询少。
• 关联条件不完整：仅靠asset_id和template_id关联，可能存在不同属性（比如不同attr_id）的错误匹配，进而出现无效数据。

修正方案 #

1. 保留主表所有记录（用LEFT JOIN）

如果想保留单表查询的全部资产记录，哪怕没有对应属性值，用左连接替代隐式内连接：

SELECT 
    aa.asset_id, aa.template_id, aa.mod_date,  
    aa.mod_user_id, aav.option_set_item_id
FROM 
    asset_attr aa
LEFT JOIN 
    asset_attr_value aav ON aa.asset_id = aav.asset_id 
                        AND aa.template_id = aav.template_id
WHERE 
    aa.mod_user_id = 11 
    AND aa.template_id = '28' /*Artwork Image View*/ 
    AND aa.mod_date > '2022-08-25'
ORDER BY 
    aa.asset_id DESC

这种方式不会过滤无属性值的资产，若存在多个属性值仍会出现多行，可根据需求进一步去重或聚合。

2. 去除重复行（用DISTINCT）

如果只需要每个资产的唯一记录，不管对应多少属性值，添加DISTINCT关键字：

SELECT DISTINCT
    aa.asset_id, aa.template_id, aa.mod_date,  
    aa.mod_user_id, aav.option_set_item_id
FROM 
    asset_attr aa
JOIN 
    asset_attr_value aav ON aa.asset_id = aav.asset_id 
                        AND aa.template_id = aav.template_id
WHERE 
    aa.mod_user_id = 11 
    AND aa.template_id = '28' /*Artwork Image View*/ 
    AND aa.mod_date > '2022-08-25'
ORDER BY 
    aa.asset_id DESC

注意：若同一资产有多个不同的option_set_item_id，DISTINCT会保留所有不同组合，行数仍可能多于单表，此时需确认要取的属性值，可搭配聚合函数（如MAX/MIN）或额外筛选条件。

3. 完善关联条件

检查asset_attr_value表结构，这类属性表通常会有attr_id字段区分不同属性，关联时加上该字段可避免错误匹配：

SELECT 
    aa.asset_id, aa.template_id, aa.mod_date,  
    aa.mod_user_id, aav.option_set_item_id
FROM 
    asset_attr aa
JOIN 
    asset_attr_value aav ON aa.asset_id = aav.asset_id 
                        AND aa.template_id = aav.template_id
                        AND aa.attr_id = aav.attr_id -- 新增attr_id关联
WHERE 
    aa.mod_user_id = 11 
    AND aa.template_id = '28' /*Artwork Image View*/ 
    AND aa.mod_date > '2022-08-25'
ORDER BY 
    aa.asset_id DESC

这能有效解决不同属性记录错误关联导致的无效数据问题。

额外排查建议 #

• 先统计asset_attr单表的结果数，再统计asset_attr_value中符合asset_id和template_id条件的记录数，对比数量关系，确认是一对多还是无匹配的问题。
• 检查asset_attr_value表是否存在脏数据（比如asset_id和template_id对应不存在于asset_attr的记录），可执行以下语句：

SELECT * FROM asset_attr_value 
WHERE template_id = '28' 
AND asset_id NOT IN (SELECT asset_id FROM asset_attr WHERE template_id = '28')

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