【mysql】出错 Subquery returns more than 1 row

2023-12-21 10:46:20

问题

查找出一下子查询返回超过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及其解决方法

文章来源:https://blog.csdn.net/u010638673/article/details/135100914
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。