在一个从两个表中拉出的视图上工作,但是在一个表中,我需要根据第三个选择一个字段或另一个字段。
Create view as
select
pens.PartNo,
pens.Title,
ranges.weight
if(pens.SpeacialOffer = 1 then pens.offer as Price else pens.Price)
from
pens, ranges
where
pens.penrange = ranges.id;
如果要约被篡改,则视图需要拉入要约,否则需要拉入价格。
最佳答案
您需要的是CASE
operator:
CASE
WHEN condition
THEN value_a
ELSE value_b
END
因此,在您的情况下:
CASE
WHEN pens.SpeacialOffer = 1
THEN pens.offer
ELSE pens.price
END
这将替换
SELECT
语句中的整个列定义,因此整个视图变为:Create View as
Select
pens.PartNo,
pens.Title,
ranges.weight,
Case
When pens.SpeacialOffer = 1
Then pens.offer
Else pens.price
End as Price
From
pens, ranges
Where
pens.penrange = ranges.id;