Update t1
Set t1.val1=t2.val1, t1.val2=t2.val2
from tab1 t1 , tab2 t2
where t2.workdate = t1.workdate and t2.cino = t1.cino
Set t1.val1=t2.val1, t1.val2=t2.val2
from tab1 t1 , tab2 t2
where t2.workdate = t1.workdate and t2.cino = t1.cino
(CASE WHEN a.item_sort='0' THEN convert(decimal(18,2),a.pro_famount)
ELSE (
SELECT SUm(
case [item_sort] when '1' then convert(decimal(18,2),pro_famount) when '0' then convert(decimal(18,2),pro_famount) else -convert(decimal(18,2),pro_famount) end
) from x_proinoutitem where [item_id] between isnull(( select max([item_id]) from x_proinoutitem where
[item_id]<=a.[item_id] and [item_sort]='0'and pro_id=a.pro_id and pro_stock=a.pro_stock),0) and a.[item_id] and pro_id=a.pro_id and pro_stock=a.pro_stock
) END) as '结存数量',
(case when b.pro_costsort='1' then
(CASE WHEN a.item_sort='0' THEN convert(decimal(18,2),a.pro_famount)*convert(decimal(18,2),a.pro_price)
ELSE (
SELECT SUm(
case [item_sort] when '1' then convert(decimal(18,2),pro_famount)*convert(decimal(18,2),pro_price) when '0' then convert(decimal(18,2),pro_famount)*convert(decimal(18,2),pro_price) else -convert(decimal(18,2),pro_famount)*convert(decimal(18,2),pro_price) end
) from x_proinoutitem where [item_id] between isnull(( select max([item_id]) from x_proinoutitem where
[item_id]<=a.[item_id] and [item_sort]='0'and pro_id=a.pro_id and pro_stock=a.pro_stock),0) and a.[item_id] and pro_id=a.pro_id and pro_stock=a.pro_stock
) END)/(case when (CASE WHEN a.item_sort='0' THEN convert(decimal(18,2),a.pro_famount)
ELSE (
SELECT SUm(
case [item_sort] when '1' then convert(decimal(18,2),pro_famount) when '0' then convert(decimal(18,2),pro_famount) else -convert(decimal(18,2),pro_famount) end
) from x_proinoutitem where [item_id] between isnull(( select max([item_id]) from x_proinoutitem where
[item_id]<=a.[item_id] and [item_sort]='0'and pro_id=a.pro_id and pro_stock=a.pro_stock),0) and a.[item_id] and pro_id=a.pro_id and pro_stock=a.pro_stock
) END)=0 then 1 else (CASE WHEN a.item_sort='0' THEN convert(decimal(18,2),a.pro_famount)
ELSE (
SELECT SUm(
case [item_sort] when '1' then convert(decimal(18,2),pro_famount) when '0' then convert(decimal(18,2),pro_famount) else -convert(decimal(18,2),pro_famount) end
) from x_proinoutitem where [item_id] between isnull(( select max([item_id]) from x_proinoutitem where
[item_id]<=a.[item_id] and [item_sort]='0'and pro_id=a.pro_id and pro_stock=a.pro_stock),0) and a.[item_id] and pro_id=a.pro_id and pro_stock=a.pro_stock
) END) end) when b.pro_costsort='2' then (sum(((case when tb.num>ta.num then ta.num else tb.num end)-
(case when ta.num-convert(decimal(18,2),ta.pro_famount)<tb.num-convert(decimal(18,2),tb.pro_famount) then tb.num-convert(decimal(18,2),tb.pro_famount) else ta.num-convert(decimal(18,2),ta.pro_famount) end))*convert(decimal(18,2),ta.pro_price)))
else (select pro_famount from x_proinoutitem where item_id=a.pro_pitem and pro_id=a.pro_id and pro_stock=a.pro_stock) end) as '成本'
FROM x_proinoutitem a inner join (select *, num=(select isnull(sum(convert(decimal(18,2),pro_famount)),0) from x_proinoutitem where item_id!>m.item_id and item_sort='1' and pro_id=m.pro_id and pro_stock=m.pro_stock) from x_proinoutitem m)ta on a.pro_id=ta.pro_id and a.item_id=ta.item_id inner join (select *, num=(select isnull(sum(convert(decimal(18,2),pro_famount)),0) from x_proinoutitem where item_id!>n.item_id and item_sort='1' and pro_id=n.pro_id and pro_stock=n.pro_stock) from x_proinoutitem n)tb on a.pro_id=tb.pro_id and a.item_id=tb.item_id inner join x_product b on a.pro_id=b.pro_id where a.pro_id='20412' group by a.pro_id,a.item_id,a.pro_stock,a.item_sort,a.pro_price,a.pro_famount,b.pro_costsort,tb.item_id,tb.pro_id,tb.pro_famount,a.pro_pitem order by a.item_id asc
Update t1
Set t1.val1=t2.val1, t1.val2=t2.val2
from tab1 t1 , (select xxxxxxxxxxx) t2
where t2.workdate = t1.workdate and t2.cino = t1.cino
慢的话再调索引