我需要根据某种优先级从表中选择数据,如下所示:
select product, price from table1 where project = 1
-- pseudo: if no price found, do this:
select product, price from table1 where customer = 2
-- pseudo: if still no price found, do this:
select product, price from table1 where company = 3
也就是说,如果我发现有3种产品的价格基于project = X
,我不想在customer = Y
上选择。我只想返回结果3行,然后就完成了。
在SQL中应该如何做这样的事情呢?对伪if语句使用某种CASE语句?做一个工会或其他一些聪明的事情?
编辑:我使用的是MS SQL。
谢谢!
发布于 2013-02-26 17:57:37
您可以执行以下sql查询
IF ((SELECT COUNT(*) FROM table1 WHERE project = 1) > 0)
SELECT product, price FROM table1 WHERE project = 1
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 2) > 0)
SELECT product, price FROM table1 WHERE project = 2
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 3) > 0)
SELECT product, price FROM table1 WHERE project = 3
发布于 2013-02-26 17:56:43
CASE语句最接近SQL中的IF语句,并且在所有版本的SQL Server上都受支持:
SELECT CASE <variable>
WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END
FROM <table>
发布于 2013-02-26 18:00:47
不用使用EXISTS
和COUNT
,只需使用@@ROWCOUNT
select product, price from table1 where project = 1
IF @@ROWCOUNT = 0
BEGIN
select product, price from table1 where customer = 2
IF @@ROWCOUNT = 0
select product, price from table1 where company = 3
END
https://stackoverflow.com/questions/15085990
复制相似问题