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對應結果圖片：

總結

以上是生活随笔為你收集整理的oracle单行子查询返回多个行 order by,单行子查询返回多个行 Issue分析求助的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。