mssql - 如果第一个表没有结果，则从另一个表中选择 [英] mssql - select from another table if no results from the first table

问题描述

我必须运行查询才能找到价格.我有多个价目表，每个价目表都在自己的表中.customer_price、default_price 和 minimum_price.

I have to run a query to find a price. I have multiple price lists, each in their own table. customer_price, default_price and minimum_price.

并非所有价目表都包含所有产品，有时某个产品可能不会出现在任何价目表中，然后价格需要为价格返回 0.

not all price lists have all the products and at times a product might not appear on any price list and then the price needs to return 0 for the price.

所以我想:

select price from customer_price where customer='walmart' and product='whitebread'

如果这返回一个结果，那么一切都很好.如果结果为 0,NULL 或查询不返回任何行，我希望它运行:

if this returns a result then all is well. if the result is 0,NULL or the query returns no rows I want this to run:

select price from default_price where customer='walmart' and product='whitebread'

如果这返回一个结果，那么一切都很好.如果结果为 0,NULL 或查询不返回任何行，我只想返回 0.

if this returns a result then all is well. if the result is 0,NULL or the query returns no rows I want to simply return 0.

我不确定如何继续.我尝试了一个 case 语句，但是如果在结果中没有找到任何行，case 语句就会失败.我该怎么做或说if 0 results then

I am not sure how to proceed. I tried a case statement but the case statement fails if no row is found in the results. how can I do this or say if 0 results then

像往常一样提前致谢.

推荐答案

select top 1 price from (
    select 1 as qorder, price from customer_price where price > 0 and customer='walmart' and product='whitebread'
    union
    select 2 as qorder, price from default_price where price > 0 and customer='walmart' and product='whitebread'
    union 
    select 3 as qorder, 0 
    ) as sq
order by qOrder

这篇关于mssql - 如果第一个表没有结果，则从另一个表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！