同事反馈线上生产环境在统计报表数据时报错,测试环境没问题,对比代码相同的情况下,将异常锁定在数据问题上面,于是申请了服务器日志查询,发现了ORA_01427(单行子查询返回多个行)的报错,果然是数据问题;
报错sql大致如下:
select tu.user_name,
tu.user_code,
(select ad.addressee_name
from t_addressee_info ad
where ad.user_id = tu.user_id
and ad.status = '1') as city_name
from t_user tu
where tu.status = '1';
通过对 t_addressee_info表中同一User分组发现,该用户有两条地址数据导致报错
经查询发现其中一条数据为系统异常产生的相同地址数据,删除此条数据后恢复正常;
为安全起见,t_user与t_addressee_info为一对一的关系,但存在多条相同地址的数据,于是在子查询中添加rownumber =1 来保证系统可正常运行;但不推荐这么用,因为可能会导致查询出错误数据
select tu.user_name,
tu.user_code,
(select ad.addressee_name
from t_addressee_info ad
where ad.user_id = tu.user_id
and ad.status = '1' and rownum =1 ) as city_name
from t_user tu
where tu.status = '1';