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
