SQL怎么才能按照要求写完成?
select   I.UnitPrice,B.RootPrice,B.DiscountID   ,DiscountNum
from   Books   B,Items   I   ,DisCount   D  
where   B.PKID   =   I.PKID   and   B.DiscountID   =   D.DiscountID  


1.当RootPrice   !=   0
    三个输出都   等于RootPrice  
2.当DiscountID   !=   1
    三个输出都等于   RootPrice   *   DiscountNum
3.   当DiscountID   =   1
三个输出都等于   shuchu1   =RootPrice   *   (DiscountNum   -1);shuchu2   =RootPrice   *   (DiscountNum   -2)
shuchu3   =RootPrice   *   (DiscountNum   -3)

请问这个SQL改怎么扩充才能完成这三个要求?



------解决方案--------------------
select I.UnitPrice,
B.RootPrice,
B.DiscountID ,
DiscountNum,
shuchu1=case when RootPrice <> 0 then RootPrice
when DiscountID <> 1 then RootPrice * DiscountNum
when DiscountID=1 then RootPrice * (DiscountNum -1)
end,
shuchu2=case when RootPrice <> 0 then RootPrice
when DiscountID <> 1 then RootPrice * DiscountNum
when DiscountID=1 then RootPrice * (DiscountNum -2)
end,,
shuchu3=case when RootPrice <> 0 then RootPrice
when DiscountID <> 1 then RootPrice * DiscountNum
when DiscountID=1 then RootPrice * (DiscountNum -3)
end,
from Books B,Items I ,DisCount D
where B.PKID = I.PKID and B.DiscountID = D.DiscountID
------解决方案--------------------
--try
select I.UnitPrice,
B.RootPrice,
B.DiscountID,
DiscountNum,
一星价=case when RootPrice <> 0 then RootPrice
when RootPrice=0 and D.DiscountID <> 1 then RootPrice * DiscountNum
else RootPrice * (DiscountNum -1) end,
二星价=case when RootPrice <> 0 then RootPrice
when RootPrice=0 and D.DiscountID <> 1 then RootPrice * DiscountNum
else RootPrice * (DiscountNum -2) end,
三星价=case when RootPrice <> 0 then RootPrice
when RootPrice=0 and D.DiscountID <> 1 then RootPrice * DiscountNum
else RootPrice * (DiscountNum -3) end
from Books B,Items I ,DisCount D
where B.PKID = I.PKID and B.DiscountID = D.DiscountID