select sub_name,step_num,
(
select (case when num1=num2 then p2/num1 else p1/num1 end) as unit_price from
(
select sum(num1) as num1,sum(num2) as num2,sum(p1) as p1,sum(p2) as p2 from
(
SELECT (unit_price*inout_bz as p1,(sub_num3*inout_bz) as num1,0 as p2,0 as num2 FROM v_record1
WHERE (v_record1.date_str <@s_date) AND (v_record1.step_num = v_record.step_num)
union all
SELECT 0 as p1,0 as num1,(unit_price *inout_bz)as p2,(sub_num3*inout_bz ) as num2 FROM v_record1
WHERE (v_record1.date_str <@s_date) AND (v_record1.sub_name = v_record.sub_name)
) as temp_aa1
) as temp_unit
) as u_cost
from v_record
WHERE (date_str =@s_date) AND (inout_dep = @sear_dep) and ((js_bz = 0) OR (js_bz IS NULL))提示错误:列前缀 'v_record' 与查询中所用的表名或别名不匹配
我是要查询结存单价。但这个单价是经过条件计算得出来的 ,
就是嵌套了多次查询的后引用最外层的字段作条件,查询不能通过。
如果把最里面的条件,v_record1.step_num = v_record.step_num 改为:v_record1.step_num = '061201001'和v_record1.sub_name = v_record.sub_name
改为:v_record1.sub_name = 'h01234567'就不会出错
象这样的几次查询后处理的结果查询要怎么处理实现呀。
(
select (case when num1=num2 then p2/num1 else p1/num1 end) as unit_price from
(
select sum(num1) as num1,sum(num2) as num2,sum(p1) as p1,sum(p2) as p2 from
(
SELECT (unit_price*inout_bz as p1,(sub_num3*inout_bz) as num1,0 as p2,0 as num2 FROM v_record1
WHERE (v_record1.date_str <@s_date) AND (v_record1.step_num = v_record.step_num)
union all
SELECT 0 as p1,0 as num1,(unit_price *inout_bz)as p2,(sub_num3*inout_bz ) as num2 FROM v_record1
WHERE (v_record1.date_str <@s_date) AND (v_record1.sub_name = v_record.sub_name)
) as temp_aa1
) as temp_unit
) as u_cost
from v_record
WHERE (date_str =@s_date) AND (inout_dep = @sear_dep) and ((js_bz = 0) OR (js_bz IS NULL))提示错误:列前缀 'v_record' 与查询中所用的表名或别名不匹配
我是要查询结存单价。但这个单价是经过条件计算得出来的 ,
就是嵌套了多次查询的后引用最外层的字段作条件,查询不能通过。
如果把最里面的条件,v_record1.step_num = v_record.step_num 改为:v_record1.step_num = '061201001'和v_record1.sub_name = v_record.sub_name
改为:v_record1.sub_name = 'h01234567'就不会出错
象这样的几次查询后处理的结果查询要怎么处理实现呀。
(
select
(case num1 when num2 then p2/num1 else p1/num1 end) as unit_price
from
(select
sum(num1) as num1,sum(num2) as num2,sum(p1) as p1,sum(p2) as p2
from (SELECT
(unit_price*inout_bz as p1,(sub_num3*inout_bz) as num1,0 as p2,0 as num2
FROM v_record1
WHERE (v_record1.date_str <@s_date) AND (v_record1.step_num = v_record.step_num)
union all
SELECT 0 as p1,0 as num1,(unit_price *inout_bz)as p2,(sub_num3*inout_bz ) as num2
FROM v_record1
WHERE (v_record1.date_str <@s_date) AND (v_record1.sub_name = v_record.sub_name)
) as temp_aa1
) as temp_unit
) as u_cost
from v_record
WHERE (date_str =@s_date) AND (inout_dep = @sear_dep) and ((js_bz = 0) OR (js_bz IS NULL))逻辑不对劲!
比如 产品A 编号是0001桶 15KG 配好后单价是10元,领用出库5KG单价11元
产品A 编号是0002桶 20KG 配好后单价是9元,领用出库20单价11元
现在最后领用:
产品A 编号是0001桶 剩下的10KG,现在要查询这次领用的是不是领完了。如果是就要取产品A所结存下来的就根据产品名称计算单价(即(20*9+15*10-20*11-5*11)/10)所有的加起来,如果不是最后结存即根据编号计算单价:((15*10-5*11)/10),只对编号001
我上面的查询就是要实现这样一个目的。