有3个表分别为Part,LRatings, OrderLine.
LRatings里包括'RARE','NORMAL','FREQUENT'.
需要更新当表格OrderLine里的LQuantity=0时,表格Part里的PRating=NULL.
需要更新当表格OrderLine里的LQuantity>0并且<=100时,表格Part里的PRating='RARE'.
需要更新当表格OrderLine里的LQuantity>100并且<=1000时,表格Part里的PRating='NORMAL'.
需要更新当表格OrderLine里的LQuantity>1000时,表格Part里的PRating='FREQUENT'.以下是我自己写的update语句,但是很不简化,求合成一句话的update语句,谢谢啦!UPDATE Part
SET PRating = NULL
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity = 0); UPDATE Part
SET PRating = (SELECT 'RARE' FROM LRatings)
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 0 AND OrderLine.LQuantity <= 100 );UPDATE Part
SET PRating = (SELECT 'NORMAL' FROM LRatings)
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 100 AND OrderLine.LQuantity <= 1000 );UPDATE Part
SET PRating = (SELECT 'FREQUENT' FROM LRatings)
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 1000 );
LRatings里包括'RARE','NORMAL','FREQUENT'.
需要更新当表格OrderLine里的LQuantity=0时,表格Part里的PRating=NULL.
需要更新当表格OrderLine里的LQuantity>0并且<=100时,表格Part里的PRating='RARE'.
需要更新当表格OrderLine里的LQuantity>100并且<=1000时,表格Part里的PRating='NORMAL'.
需要更新当表格OrderLine里的LQuantity>1000时,表格Part里的PRating='FREQUENT'.以下是我自己写的update语句,但是很不简化,求合成一句话的update语句,谢谢啦!UPDATE Part
SET PRating = NULL
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity = 0); UPDATE Part
SET PRating = (SELECT 'RARE' FROM LRatings)
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 0 AND OrderLine.LQuantity <= 100 );UPDATE Part
SET PRating = (SELECT 'NORMAL' FROM LRatings)
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 100 AND OrderLine.LQuantity <= 1000 );UPDATE Part
SET PRating = (SELECT 'FREQUENT' FROM LRatings)
WHERE EXISTS(SELECT LQuantity FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 1000 );
SET PRating =
case when (SELECT 1 FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity = 0)=1 then NULL
case when (SELECT 1 FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 0 AND OrderLine.LQuantity <= 100 ) =1 then 'RARE'
case when (SELECT 1 FROM OrderLine WHERE Part.PNumber = OrderLine.LPart AND OrderLine.LQuantity > 1000 )=1 then 'FREQUENT'
end
楼主,条件不同,整合不了一句的