SQL服务器中的case语句 [英] Case statement in SQL server

问题描述

大家好，

需要从多个查询中获取价格。



如果第一个查询价格为0则执行第二个查询。<如果第二个查询价格是0，那么
然后执行第三个查询。



我尝试了case case。但它没有按预期工作。



请帮我解决这个问题。



什么我试过了：

Hi Guys,
Need to get price from multiple queries.

if 1st query price is 0 then execute 2nd query.
if 2nd query price is 0 then execute 3rd query.

I tried with case statement. but it's not working as expected.

Please help me to fix this.

What I have tried:

select R.Id,(case 
when R.Price !=0.00 then R.Price
when R.Price =0.00
then
(select max(C.price) from ItemOption IO, Options O, ItemOptionChoices C where IO.OptionId=O.OptionId 
and O.OptionId = C.OptionId and O.MultipleSelections='Radio Buttons' and MenuItemId=R.Id)
 when R.Price =0.00
 then
 (select max(price) as variantprice from ItemVariation IV where  IV.ItemId=R.Id)
 end) as Price
from RestaurantMenu as R where CategoryId='5b7a3bfd-427f-4813-a54e-ff32f47b95a4' and EntityType='Item'
 Order by CategoryOrder

推荐答案

尝试使用公用表表达式而不是使用这些子查询。

请注意，您已列出条件 WHEN R.Price = 0.00 两次。

这是真的，重新ally老式表达联接的方式 - 不要这样做。



从你给我们的小信息中得到以下查询可能工作。

Rather than using those sub-queries try using Common Table Expressions.
Note that you have listed the condition WHEN R.Price =0.00 twice.
That is a really, really old fashioned way of expressing joins - don't do it that way.

From the little information you have given us the following query might work.

;with c1 as
(
	select max(C.price) as C1Price, MenuItemId
	from ItemOption IO, 
	join Options O ON IO.OptionId=O.OptionId
	join ItemOptionChoices C ON  O.OptionId = C.OptionId
	where O.MultipleSelections='Radio Buttons' 
), c2 as 
(
	select max(price) as c2Price, ItemId as variantprice from ItemVariation IV 
)
select R.Id,
	(SELECT TOP 1 P FROM (VALUES (R.Price),(C1Price),(C2Price)) as tmp(P) WHERE P <> 0 ORDER BY P ASC) as maxPrice
from RestaurantMenu as R
left join c1 ON MenuItemId=R.Id
left join C2 ON IV.ItemId=R.Id
where CategoryId='5b7a3bfd-427f-4813-a54e-ff32f47b95a4' and EntityType='Item'
 Order by CategoryOrder

注意使用内联临时tabl e获取值 - 通过 sven [ ^ ]



这可能需要一些额外的工作来处理空值。

Note the use of an in-line temporary table to get the values - adapted from a solution by sven[^]

This may some extra work to handle nulls.

这篇关于SQL服务器中的case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！