【mysql】出错 Subquery returns more than 1 row

问题

查找出一下子查询返回超过1行

SELECT cc.id,DATE_FORMAT(cc.CREATE_TIME,'%Y%m%d') as day_id, 
IFNULL((select f.source FROM strong_contact_fea f where f.id = cc.id and STR_SPEC_ID='S2023091145'),'10501') as strong_prod_level_1,
IFNULL((select f.source from strong_contact_fea f where f.id = cc.id and STR_SPEC_ID='S2023091146'),'1050101') as strong_prod_level_2
		FROM strong_contact cc
		WHERE 1=1
		and cc.CREATE_TIME IS NOT NULL
		and cc.CREATE_TIME BETWEEN '2023-12-19 00:00:00' and '2023-12-20 00:00:00' 

• 返回

子查询返回超过1行
> IP:PORT/STRONG | Subquery returns more than 1 row

排查

查询重复数据定位问题id单

	select f.id,count(*) num from strong_contact_fea f where  1=1  and STR_SPEC_ID='S2023091145' AND f.id in 
	(
	SELECT
		cc.id
		FROM strong_contact cc
		WHERE 1=1
		and cc.CREATE_TIME IS NOT NULL
		and cc.CREATE_TIME BETWEEN '2023-12-19 00:00:00' and '2023-12-20 00:00:00' 
	) group by f.ID HAVING num>1

处理

子查询添加limit 1

SELECT cc.id,DATE_FORMAT(cc.CREATE_TIME,'%Y%m%d') as day_id, 
IFNULL((select f.source FROM strong_contact_fea f where f.id = cc.id and STR_SPEC_ID='S2023091145' limit 1),'10501') as strong_prod_level_1,
IFNULL((select f.source from strong_contact_fea f where f.id = cc.id and STR_SPEC_ID='S2023091146' limit 1),'1050101') as strong_prod_level_2
		FROM strong_contact cc
		WHERE 1=1
		and cc.CREATE_TIME IS NOT NULL
		and cc.CREATE_TIME BETWEEN '2023-12-19 00:00:00' and '2023-12-20 00:00:00' 

根治

上面是写兼容查询limit 1方式处理，但是真正的源头是来自逻辑代码错误导致数据重复产生，功能修复，原先数据毕竟生产珍贵数据，所以我们这里需要进行去重处理。

注意：生产去重进行删除数据前，先查询备份，然后在删除；备份，就是保命。

• TASK231220lukcy-2023-12-20_测试.sql

/* 查询关联重复的数据 */
select tb.biz_id as key_biz_id, tb.str_spec_id as key_str_spec_id, ccf.* from (
  /* 根据biz_id和str_spec_id关联工单 begin */
  select f.biz_id,f.str_spec_id,count(*) num from strong_contact_fea f where 1=1 and str_spec_id in ('s2023091145')
	and f.biz_id in ( select cc.biz_id from strong_contact cc
		where 1=1 and cc.create_time is not null
		and cc.create_time between '2023-12-20 00:00:00' and '2023-12-21 00:00:00'
	) group by f.biz_id ,f.str_spec_id having num>1
  /* 根据biz_id和str_spec_id关联工单 end */
) tb left join strong_contact_fea ccf on tb.biz_id = ccf.biz_id and tb.str_spec_id = ccf.str_spec_id where 1=1;

/*先备份导出，然后删除（select *）改成（delete）*/
select * from strong_contact_fea where str_id in (
    /* 最终删除的查询数据【str_id】*/
    select ccf.str_id from (
      /* 根据biz_id和str_spec_id关联工单 begin */
      select f.biz_id,f.str_spec_id,count(*) num from strong_contact_fea f where 1=1 and str_spec_id in ('s2023091145')
        and f.biz_id in ( select cc.biz_id from strong_contact cc
            where 1=1 and cc.create_time is not null
            and cc.create_time between '2023-12-20 00:00:00' and '2023-12-21 00:00:00'
        ) group by f.biz_id ,f.str_spec_id having num>1
      /*根据biz_id和str_spec_id关联工单 end */
    ) tb left join strong_contact_fea ccf on tb.biz_id = ccf.biz_id and tb.str_spec_id = ccf.str_spec_id where 1=1
    and str_id not in (
        select min(str_id) as str_id from (
          /* 根据biz_id和str_spec_id关联工单 begin */
          select f.biz_id,f.str_spec_id,count(*) num from strong_contact_fea f where 1=1 and str_spec_id in ('s2023091145')
            and f.biz_id in ( select cc.biz_id from strong_contact cc
                where 1=1 and cc.create_time is not null
                and cc.create_time between '2023-12-20 00:00:00' and '2023-12-21 00:00:00'
            ) group by f.biz_id ,f.str_spec_id having num>1
          /* 根据biz_id和str_spec_id关联工单 end */
        ) tb left join strong_contact_fea ccf on tb.biz_id = ccf.biz_id and tb.str_spec_id = ccf.str_spec_id
        where 1=1 group by tb.biz_id,tb.str_spec_id
    )
);

写法拆解

1. 数据量大，查询需要时间索引，所以用传统方式查询，防止索引失效性能问题
   and cc.CREATE_DATE BETWEEN '2023-12-20 00:00:00' and '2023-12-21 00:00:00'
2. 查询重复，查询出重复数据
   select f.id_a,f.id_b,count(*) num from table_a f where 1=1 group by f.id_a,f.id_b having num>1
3. 查询关联，查询关键业务单号关联数据 select a.*,b.* from table_a a left join table_b b on a.id_1=b.id_1 and a.type=b.type
4. 查询排查，查询数据保留其中一条，唯一值最小或者最大值处理
   select MIN(FEA_ID) AS FEA_ID from (对应表) WHERE 1=1 GROUP BY tb.cont_id,tb.FEA_SPEC_ID
5. 查询移除，查询移除的数据
   select * FROM strong_contact_fea where str_id in () and str_id not in (不允许移除的)
6. 查询备份
   导出、备份都可以
7. 查询删除，处理数据
   delete from (查询移除数据)

参考

MySQL出错信息: Subquery returns more than 1 row及其解决方法