您能在 SQL 中使用 if-then-else 逻辑吗？

我需要根据某种优先级从表中选择数据，如下所示:

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。

谢谢！

368361 次浏览
小开

The CASE statement is the closest to an IF statement in SQL, and is supported on all versions of SQL Server:

SELECT CASE <variable>
WHEN <value>      THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END
FROM <table>
小开
最佳答案

You can make the following sql query

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
小开

Instead of using EXISTS and COUNT just use @@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
小开

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
小开

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
小开

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;

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;

An SQLfiddle to test with.

Alternately, you can combine it all into one SELECT to simplify it for the optimizer;

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;

Another SQLfiddle.

小开

--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

提交答案