ORA-01427:单行子查询返回多个行报错记录

最新推荐文章于 2023-07-18 15:34:23 发布

原创最新推荐文章于 2023-07-18 15:34:23 发布 · 1.2w 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一种常见的SQL错误：ORA-01427，即单行子查询返回多行的问题，并提供了修改SQL语句的具体方法，通过添加rownum限制子查询返回结果的数量，从而避免错误。

前几天写了如下一个SQL，然后报错ORA-01427:单行子查询返回多个行。

select 
       psi.extend_3 as op_time,    ----1.账期(获取昨天数据，格式yyyyMMdd)
       substr(psi.provinceCode,0,3) as provinceCode, ----2.省公司编码
       psi.serviceChannel as serviceChannel,   ----3.触点渠道
       psi.serviceid as serviceId,        ----4.测评流水号
       psi.extend_2 as custResponse,    ----5.是否测评，此数据都是参与测评的数据
       psi.customerno,   ----6.用户设备号
       '4' as serviceMode,    ----7.测评方式，固定写死4
       '4013' as serviceEntry,    ----8.测评入口，写死4013(微信公众)
       (select to_char(s_create_time,'yyyymmddhh24miss') s_create_time from p_service_info ppp where ppp.orderid = psr.orderid) as requestTime,     ----9.测评触发时间（严格按照规范）
       (select to_char(create_time,'yyyymmddhh24miss') create_time from p_service_result qqq where qqq.orderid = psr.orderid) as finishTime,     ----10.测评完成时间（严格按照规范）
       psr.star as serviceResult,    ----11.人员评价结果
       psr.content as resonDesWaiter,    ----12.人员评价标签描述词
       psr.extend_3 as resonGroupWaiter, ----13.人员评价原因标签归类(这个需要再次确认)
       psr.serstar as companyResult,    ----14.企业评价结果
       psr.sercontent as resonDesCompany,    ----15.企业评价标签描述词
       psr.extend_4 as resonGroupCompany,    ----16.企业评价原因标签归类(这个需要再次确认)
       psr.botcontent as customerAdvice   ----17.客户意见
     
from p_service_info psi
left join  p_service_result psr
on psr.orderid = psi.orderid
where psr.create_time between 
to_timestamp('07-03-19 00:00:00.000000000','dd-mm-yy hh24:mi:ss.ff9') and to_timestamp('07-03-19 23:59:59.000000000','dd-mm-yy hh24:mi:ss.ff9')

找到了问题，是由于SQL中的子查询，出现了返回多条记录的情况，

试着在子查询中加入rownum<2,也就是限制返回一行数据。

更改后SQL：

select 
       psi.extend_3 as op_time,    ----1.账期(获取昨天数据，格式yyyyMMdd)
       substr(psi.provinceCode,0,3) as provinceCode, ----2.省公司编码
       psi.serviceChannel as serviceChannel,   ----3.触点渠道
       psi.serviceid as serviceId,        ----4.测评流水号
       psi.extend_2 as custResponse,    ----5.是否测评，此数据都是参与测评的数据
       psi.customerno,   ----6.用户设备号
       '4' as serviceMode,    ----7.测评方式，固定写死4
       '4013' as serviceEntry,    ----8.测评入口，写死4013(微信公众)
       (select to_char(s_create_time,'yyyymmddhh24miss') s_create_time from p_service_info ppp where ppp.orderid = psr.orderid and rownum=1) as requestTime,     ----9.测评触发时间（严格按照规范）
       (select to_char(create_time,'yyyymmddhh24miss') create_time from p_service_result qqq where qqq.orderid = psr.orderid and rownum=1) as finishTime,     ----10.测评完成时间（严格按照规范）
       psr.star as serviceResult,    ----11.人员评价结果
       psr.content as resonDesWaiter,    ----12.人员评价标签描述词
       psr.extend_3 as resonGroupWaiter, ----13.人员评价原因标签归类(这个需要再次确认)
       psr.serstar as companyResult,    ----14.企业评价结果
       psr.sercontent as resonDesCompany,    ----15.企业评价标签描述词
       psr.extend_4 as resonGroupCompany,    ----16.企业评价原因标签归类(这个需要再次确认)
       psr.botcontent as customerAdvice   ----17.客户意见
     
from p_service_info psi
left join  p_service_result psr
on psr.orderid = psi.orderid
where psr.create_time between 
to_timestamp('07-03-19 00:00:00.000000000','dd-mm-yy hh24:mi:ss.ff9') and to_timestamp('07-03-19 23:59:59.000000000','dd-mm-yy hh24:mi:ss.ff9')