select *,
case RoHS when 1 then
case when right(itemold,3)='-CN' then left(itemold,len(itemold)-3)+'-H-CN'
else itemold+'-H'
end
else itemold
end as AddedMark_INO
from Products
case RoHS when 1 then
case when right(itemold,3)='-CN' then left(itemold,len(itemold)-3)+'-H-CN'
else itemold+'-H'
end
else itemold
end as AddedMark_INO
from Products
case RoHS when 1 then
case when itemold like '%-H-CN' then itemold
when itemold like '%-CN' then left(itemold,len(itemold)-3)+'-H-CN'
when itemold like '%-H' then itemold
else itemold+'-H'
end
else itemold
end as AddedMark_INO
from Products
set itemold = case when right(rtrim(itemold),3)='-CN' then left(itemold,len(rtrim(itemold))-3) +'-H-CN'
else rtrim(itemold)+'-H'
end
where RoHS = '1'
and right(rtrim(itemold),2) <> '-H'
真沒注意到 Case 可以嵌套,請問Case嵌套時,條件為什麼要不設?你的語句
case when itemold like '%-H-CN' then itemold
when itemold like '%-CN' then left(itemold,len(itemold)-3)+'-H-CN'
when itemold like '%-H' then itemold
else itemold+'-H'
end
這樣比較我會很痛苦,因為是兩個2萬條記錄的表在進行關聯查詢,N多 like 之後執行時接近於死機,不過方法我已經知道了,謝謝!!