ORA-01427: 单行子查询返回多个行

今天在解决问题时遇到了这个问题，ORA-01427: 单行子查询返回多个行

后来发现是因为在子查询中SQL语句返回多条结果。

子查询：

select distinct CUS_GOODSNO from v_biz_goods_match d 
where d.SUP_GOODSNO = i.GOODSID and d.flag = 0 and d.deleted = 0 and d.CUS_ORGID = i.CUSTOMCODEINSYS and d.SUP_ORGID = 'S00000001'

SQL语句：

select 
i.create_date,
i.create_user,
i.id, 
i.update_date,
i.update_user,
i.deleted,
(select distinct CUS_GOODSNO from v_biz_goods_match d 
where d.SUP_GOODSNO = i.GOODSID and d.flag = 0 and d.deleted = 0 and d.CUS_ORGID = i.CUSTOMCODEINSYS and d.SUP_ORGID = 'S00000001' and rownum=1) cusgoodsno,
from BIZ_HRHN_RETURN_INVOICE i 
where i.CUSTOMCODEINSYS = 'H00000012' order by i.create_date desc;

在子查询中加上and rownum = 1，强制返回一条结果。