oracle单行子查询返回多个行 order by,单行子查询返回多个行 Issue分析求助

with order_base as --获取订单基础情况

(

select ou.order_key order_key, ou.order_quantity_i, ood.dispatch_time_t, ou.part_number_s, 'I032ZZ01' pline_name_s

from order_uv ou left join at_as_om_orderdispatchstatus ood on ou.order_key = ood.order_54

union all

select mo.atr_key order_key, mo.order_quantity_i, mo.dispatch_time_t, mo.part_number_s, to_char(mo.pline_name_s) pline_name_s

from at_as_mm_order_plan mo

)

,

plan_quantity_base as --根据订单派发时间确定订单派发的班次

(

select to_char(ob.dispatch_time_t,'yyyymmdd') work_day, dispatch_time_t,

(

select su.description shift_name

from at_as_sm_calendar sc, shift_uv su

where su.shift_key = sc.shift_198

and category_s = 'MFG' and target_s = ob.pline_name_s and target_type_s = 'Production Line'

and ob.dispatch_time_t >= sc.start_time_t and ob.dispatch_time_t <= sc.end_time_t

and to_char(ob.dispatch_time_t,'yyyymmdd') = to_char(sc.start_time_t,'yyyymmdd')

) shift_name,

ob.pline_name_s, ob.order_quantity_i

from order_base ob

)

--select * from plan_quantity_base;

--Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S

--From Plan_Quantity_Base Pqb

--group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S;

--,

--plan_quantity as ( --根据订单派发时间计算生产线的班次计划产量

select to_char(Pqb.dispatch_time_t,'yyyymmdd') work_day, pqb.shift_name, pqb.pline_name_s, sum(pqb.order_quantity_i) plan_quantity

From Plan_Quantity_Base Pqb

--Where Pqb.Pline_Name_S Like Decode(roduction_Line,'ALL','%%',Null,'%%',roduction_Line)

--and pqb.shift_name like decode(:shift,'ALL','%%',:shift)

group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S

--)

问题描述：

sql运行到 plan_quantity 时报错：单行子查询返回多个行

1、单独对每个语句块写sql语句查询，排查下来发现问题发生地方在 plan_quantity 上；

2、运行语句(Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S

From Plan_Quantity_Base Pqb)和(select * from plan_quantity_base)都没问题；

3、运行语句

(Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S

From Plan_Quantity_Base Pqb

group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S)

和

( select to_char(Pqb.dispatch_time_t,'yyyymmdd') work_day, pqb.shift_name, pqb.pline_name_s, sum(pqb.order_quantity_i) plan_quantity

From Plan_Quantity_Base Pqb

--Where Pqb.Pline_Name_S Like Decode(roduction_Line,'ALL','%%',Null,'%%',roduction_Line)

--and pqb.shift_name like decode(:shift,'ALL','%%',:shift)

group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S)

报错：单行子查询返回多个行；

问题疑问：

1、在 plan_quantity 语句块中，已经group by 了，并且查询的列中并没有子查询，为什么会发生这种错误？

请大家帮忙分析下，实在没有分析出来。

问题描述2对应结果图片：

问题描述3对应结果图片：