MySQL按Prod_Id筛选目标数据：单条保留/多条取Config_Type=1

原始prod_config表数据 #

表数据规则 #

• Prod_Id可关联单个或多个Config_Id（Config_Type为1或2）
• 若Prod_Id仅出现一次，其Config_Type必为2
• 若Prod_Id出现多次，仅存在一条Config_Type为1的记录

查询需求 #

保留所有仅出现一次的Prod_Id记录，对出现多次的Prod_Id仅保留Config_Type为1的记录，预期结果如下：

预期查询结果 #

解决方案 #

方法一：使用窗口函数 #

利用ROW_NUMBER()窗口函数按Prod_Id分组，优先保留Config_Type=1的记录，同时保留仅出现一次的记录：

WITH prod_stats AS (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY Prod_Id) AS occur_count,
        ROW_NUMBER() OVER (PARTITION BY Prod_Id ORDER BY Config_Type) AS row_num
    FROM prod_config
)
SELECT Prod_Id, Config_Id, Config_Type
FROM prod_stats
WHERE occur_count = 1 OR (occur_count > 1 AND row_num = 1);

说明：Config_Type=1的数值更小，排序后会排在同组最前面，row_num=1刚好对应这条记录；仅出现一次的记录直接通过occur_count=1筛选保留。

方法二：子查询关联统计 #

先统计每个Prod_Id的出现次数，再关联原表筛选符合条件的记录：

SELECT pc.Prod_Id, pc.Config_Id, pc.Config_Type
FROM prod_config pc
INNER JOIN (
    SELECT Prod_Id, COUNT(*) AS cnt
    FROM prod_config
    GROUP BY Prod_Id
) prod_cnt ON pc.Prod_Id = prod_cnt.Prod_Id
WHERE prod_cnt.cnt = 1 OR (prod_cnt.cnt > 1 AND pc.Config_Type = 1);

说明：子查询计算每个Prod_Id的出现次数，主查询中直接根据次数和Config_Type筛选，符合题目规则。

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