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

[云轩晓晓]

这段语句“case when (select lh.status from wh2.load_detail ld,wh2.load_header lh where ld.load_id=lh.load_id and o.order_id=ld.order_id and sc.container_id=ld.container_id ) ='Closed' then '交接完成' else '未交接' end as 交接状态”导致了报错，但是不知道怎么改，求大神帮忙
以下是整段语句：
select o.order_date as 订单日期,
o.requested_ship_date as 要求发货日期,
o.actual_ship_date as 实际发货日期,
sc.container_id as 箱号,o.order_id as 发货单号,
o.consignee_id as 店铺代码,o.c_company as 店铺,
sum(sc.total_qty) as 装箱数量,
case when sc.status = 'Closed' then '已封箱' else '未封箱' end as 封箱状态,
case when (select lh.status from wh2.load_detail ld,wh2.load_header lh where ld.load_id=lh.load_id and o.order_id=ld.order_id and sc.container_id=ld.container_id ) ='Closed' then '交接完成' else '未交接' end as 交接状态
from wh2.orders o, wh2.shipment_container sc
where o.order_id=sc.order_id
and o.actual_ship_date between '2017-10-01' and '2017-10-15'
and sc.total_qty>0
group by o.order_date,o.requested_ship_date,o.actual_ship_date,o.order_id,o.consignee_id,o.c_company,sc.container_id,sc.status
order by sc.container_id

[碧水幽幽泉]

lh.status有多个值，建议加个rownum=1
[code=sql]
select o.order_date as 订单日期,
o.requested_ship_date as 要求发货日期,
o.actual_ship_date as 实际发货日期,
sc.container_id as 箱号,o.order_id as 发货单号,
o.consignee_id as 店铺代码,o.c_company as 店铺,
sum(sc.total_qty) as 装箱数量,
case when sc.status = 'Closed' then '已封箱' else '未封箱' end as 封箱状态,
case when (select lh.status
from wh2.load_detail ld,wh2.load_header lh
where ld.load_id=lh.load_id
and o.order_id=ld.order_id
and sc.container_id=ld.container_id
and rownum = 1
) ='Closed' then '交接完成'
else '未交接'
end as 交接状态
from wh2.orders o, wh2.shipment_container sc
where o.order_id=sc.order_id
and o.actual_ship_date between '2017-10-01' and '2017-10-15'
and sc.total_qty > 0
group by o.order_date,o.requested_ship_date,o.actual_ship_date,o.order_id,o.consignee_id,o.c_company,sc.container_id,sc.status
order by sc.container_id
[code]

[碧水幽幽泉]

lh.status有多个值，建议加个rownum=1

select o.order_date as 订单日期,
       o.requested_ship_date as 要求发货日期,
       o.actual_ship_date as 实际发货日期,
       sc.container_id as 箱号,o.order_id as 发货单号,
       o.consignee_id as 店铺代码,o.c_company as 店铺,
       sum(sc.total_qty) as 装箱数量,
       case when sc.status = 'Closed' then '已封箱' else '未封箱' end as 封箱状态,
       case when (select lh.status 
	                from wh2.load_detail ld,wh2.load_header lh 
				   where ld.load_id=lh.load_id 
				     and o.order_id=ld.order_id 
					 and sc.container_id=ld.container_id
					 and rownum = 1
				  ) ='Closed' then '交接完成'
			else '未交接' 
	    end as 交接状态
  from wh2.orders o, wh2.shipment_container sc
 where o.order_id=sc.order_id 
   and o.actual_ship_date between '2017-10-01' and '2017-10-15'
   and sc.total_qty > 0
 group by o.order_date,o.requested_ship_date,o.actual_ship_date,o.order_id,o.consignee_id,o.c_company,sc.container_id,sc.status
 order by sc.container_id
 

[「已注销」]

这个要结合业务状态，首先确定为什么存在多条重复记录，而且要取那一条，看那种优先，还有你这种是OLAP系统的吧？你这样查询会很慢的，建议把关联子查询写在from后面

[lipiaolpt]

用max试试???

[qq_36611253]

瞎写了下，没有运行，楼主试试看 select a.order_date as 订单日期, a.requested_ship_date as 要求发货日期, a.actual_ship_date as 实际发货日期, a.container_id as 箱号, a.order_id as 发货单号, a.consignee_id as 店铺代码, a.c_company as 店铺, sum(a.total_qty) as 装箱数量, a.封箱状态 as 封箱状态, a.交接状态 as 交接状态 from ((select load_id, order_id, container_id from wh2.load_detail)ld inner join (select order_date, requested_ship_date, actual_ship_date, consignee_id, order_id, c_company from wh2.orders where actual_ship_date between '2017-10-01' and '2017-10-15')o on ld.order_id=o.order_id inner join (select container_id, total_qty, case when status='Closed' then '已封箱' else '未封箱'end as 封箱状态 from wh2.shipment_container where total_qty>0)sc on ld.container_id=sc.container_id inner join (select load_id,case status when 'Closed' then '交接完成' else '未交接' end as 交接状态 from wh2.load_header)lh on Id.load_id=lh.load_id)a group by a.order_date, a.requested_ship_date, a.actual_ship_date, a.container_id, a.order_id, a.consignee_id, a.c_company, a.封箱状态, a.交接状态

[Space1208]

(select lh.status from wh2.load_detail ld,wh2.load_header lh where ld.load_id=lh.load_id and o.order_id=ld.order_id and sc.container_id=ld.container_id ) ='Closed' ---case when lh.status='Closed' then 'Closed' else '1' end

[Space1208]

对lh.status 加个case when 可以试试