你能在 SQL 中有 if-then-else 逻辑吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15085990/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can you have if-then-else logic in SQL?
提问by Cotten
I need to do select data from a table based on some kind of priority like so:
我需要根据某种优先级从表中选择数据，如下所示：
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
That is, if I found 3 products with prices based on project = X, I don't want to select on customer = Y. I just want to return the resulting 3 rows and be done.
也就是说，如果我发现 3 个产品的价格基于project = X，我不想选择customer = Y。我只想返回结果 3 行并完成。
How are you supposed to do stuff like this in SQL? Use some kind of CASE-statement for the pseudo-if's? Do a union or some other smart thing?
你应该如何在 SQL 中做这样的事情？对伪 if 使用某种 CASE 语句？做工会或其他一些聪明的事情吗？
Edit: I'm using MS SQL.
编辑：我正在使用 MS SQL。
Thanks!
谢谢！
回答by Alex
You can make the following sql query
您可以进行以下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
回答by Rohit
The CASE statement is the closest to an IF statement in SQL, and is supported on all versions of SQL Server:
CASE 语句最接近 SQL 中的 IF 语句，并且所有版本的 SQL Server 都支持：
SELECT CASE <variable>
WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END
FROM <table>
回答by Andrey Gordeev
Instead of using EXISTSand COUNTjust use @@ROWCOUNT:
而不是使用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
回答by Joachim Isaksson
With SQL server you can just use a CTE instead of IF/THEN logic to make it easy to map from your existing queries and change the number of involved queries;
使用 SQL 服务器，您可以只使用 CTE 而不是 IF/THEN 逻辑，以便轻松地从现有查询进行映射并更改涉及的查询数量；
WITH cte AS (
SELECT product,price,1 a FROM table1 WHERE project=1 UNION ALL
SELECT product,price,2 a FROM table1 WHERE customer=2 UNION ALL
SELECT product,price,3 a FROM table1 WHERE company=3
)
SELECT TOP 1 WITH TIES product,price FROM cte ORDER BY a;
Alternately, you can combine it all into one SELECTto simplify it for the optimizer;
或者，您可以将它们合二为一SELECT以简化优化器；
SELECT TOP 1 WITH TIES product,price FROM table1
WHERE project=1 OR customer=2 OR company=3
ORDER BY CASE WHEN project=1 THEN 1
WHEN customer=2 THEN 2
WHEN company=3 THEN 3 END;
回答by TechDo
Please check whether this helps:
请检查这是否有帮助：
select TOP 1
product,
price
from
table1
where
(project=1 OR Customer=2 OR company=3) AND
price IS NOT NULL
ORDER BY company
回答by Lee
--Similar answer as above for the most part. Code included to test
--大部分情况与上述答案类似。包含用于测试的代码
DROP TABLE table1
GO
CREATE TABLE table1 (project int, customer int, company int, product int, price money)
GO
INSERT INTO table1 VALUES (1,0,50, 100, 40),(1,0,20, 200, 55),(1,10,30,300, 75),(2,10,30,300, 75)
GO
SELECT TOP 1 WITH TIES product
, price
, CASE WhereFound WHEN 1 THEN 'Project'
WHEN 2 THEN 'Customer'
WHEN 3 THEN 'Company'
ELSE 'No Match'
END AS Source
FROM
(
SELECT product, price, 1 as WhereFound FROM table1 where project = 11
UNION ALL
SELECT product, price, 2 FROM table1 where customer = 0
UNION ALL
SELECT product, price, 3 FROM table1 where company = 30
) AS tbl
ORDER BY WhereFound ASC
回答by mson
there is a case statement, but i think the below is more accurate/efficient/easier to read for what you want.
有一个案例陈述，但我认为下面的内容更准确/有效/更容易阅读您想要的内容。
select
product
,coalesce(t4.price,t2.price, t3.price) as price
from table1 t1
left join table1 t2 on t1.product = t2.product and t2.customer =2
left join table1 t3 on t1.product = t3.product and t3.company =3
left join table1 t4 on t1.product = t4.product and t4.project =1
