如果然后其他人
问题描述:
工作在一个视图,从两个表中拉,但是在一个表中,我需要选择一个领域或另一个取决于第三..这是如果别人有我残缺。如果然后其他人
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;
答
的使用情况,也转换查询,以显式连接的隐含而不是加入
select pens.PartNo,
pens.Title,
ranges.weight,
(Case when
pens.SpeacialOffer = 1 then
pens.offer else pens.Price
end) as Price
FROM pens,
JOIn ranges
ON pens.penrange = ranges.id;
答
这里有一种方法:
Create view as select
pens.PartNo,
pens.Title,
ranges.weight,
(pens.SpeacialOffer * pens.offer + (1 - pens.SpeacialOffer) * pens.price) as Price
from
pens,
ranges
where
pens.penrange = ranges.id;