blocks|key|370644|text|CASE语句最接近SQL中的IF语句，并且在所有版本的SQL+Server上都受支持：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|370645|SELECT+CASE+<variable>+
+++++++++++WHEN+<value>++++++THEN+<returnvalue>+
+++++++++++WHEN+<othervalue>+THEN+<returnthis>+
+++++++++++ELSE+<returndefaultcase>+
+++++++END+
++FROM+<table>+|code-block|syntax|javascript|370646|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>SELECT CASE &lt;variable&gt; 
 WHEN &lt;value&gt; THEN &lt;returnvalue&gt; 
 WHEN &lt;othervalue&gt; THEN &lt;returnthis&gt; 
 ELSE &lt;returndefaultcase&gt; 
 END 
 FROM &lt;table&gt; 
</code></pre>

blocks|key|152401|text|您可以执行以下sql查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|152402|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|code-block|syntax|javascript|152403|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can make the following sql query

<pre><code>IF ((SELECT COUNT(*) FROM table1 WHERE project = 1) &gt; 0) 
 SELECT product, price FROM table1 WHERE project = 1
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 2) &gt; 0) 
 SELECT product, price FROM table1 WHERE project = 2
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 3) &gt; 0)
 SELECT product, price FROM table1 WHERE project = 3
</code></pre>

blocks|key|152413|text|不用使用EXISTS和COUNT，只需使用@@ROWCOUNT|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|152414|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|code-block|syntax|javascript|152415|entityMap^0|4|6|B|5|L|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

Instead of using <code>EXISTS</code> and <code>COUNT</code> just use <code>@@ROWCOUNT</code>:

<pre><code>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
</code></pre>

blocks|key|152428|text|有一个case语句，但我认为下面的语句更准确/更有效/更容易读懂你想要的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|152429|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|code-block|syntax|javascript|152430|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

there is a case statement, but i think the below is more accurate/efficient/easier to read for what you want.

<pre><code>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
</code></pre>

blocks|key|370717|text|请检查这是否有帮助：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|370718|select+TOP+1
++++product,+
++++price+
from+
++++table1+
where+
++++(project=1+OR+Customer=2+OR+company=3)+AND
++++price+IS+NOT+NULL
ORDER+BY+company+|code-block|syntax|javascript|370719|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please check whether this helps:

<pre><code>select TOP 1
 product, 
 price 
from 
 table1 
where 
 (project=1 OR Customer=2 OR company=3) AND
 price IS NOT NULL
ORDER BY company 
</code></pre>

blocks|key|152515|text|使用SQL+server，您可以使用CTE而不是IF/THEN逻辑来轻松地从现有查询映射并更改涉及的查询的数量；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|152516|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;|code-block|syntax|javascript|152517|An+SQLfiddle+to+test+with。|offset|length|152518|或者，您可以将它们组合到一个SELECT中，以便为优化器简化它；|style|CODE|152519|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;|152520|Another+SQLfiddle。|152521|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!6/175c0/5|1|http://sqlfiddle.com/#!6/9fc00/1^0|0|0|0|P|0|0|E|6|0|0|0|H|1|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@$I|15|J|16|1|17]]|A|$]]|$1|K|3|L|5|6|7|18|8|@$I|19|J|1A|M|N]]|9|@]|A|$]]|$1|O|3|P|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1C|8|@]|9|@$I|1D|J|1E|1|1F]]|A|$]]|$1|S|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]]]

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;

<pre><code>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;
</code></pre>

<a href="http://sqlfiddle.com/#!6/175c0/5" rel="nofollow">An SQLfiddle to test with</a>.

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

<pre><code>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;
</code></pre>

<a href="http://sqlfiddle.com/#!6/9fc00/1" rel="nofollow">Another SQLfiddle</a>.

blocks|key|370807|text|--大多数情况下答案与上面类似。包含要测试的代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|370808|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|code-block|syntax|javascript|370809|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

--Similar answer as above for the most part. Code included to test

<pre><code>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
</code></pre>

I need to do select data from a table based on some kind of priority like so:

<pre><code>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
</code></pre>

That is, if I found 3 products with prices based on <code>project = X</code>, I don't want to select on <code>customer = Y</code>. I just want to return the resulting 3 rows and be done.

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?

Edit: I'm using MS SQL.

Thanks!

Can you have if-then-else logic in SQL?

SQL Server

我需要根据某种优先级从表中选择数据，如下所示：select product, price from table1 where project = 1-- pseudo: if no price found, do this:select product, price from table1 where customer...

问你能在SQL中使用if-then-else逻辑吗？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问你能在SQL中使用if-then-else逻辑吗？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问你能在SQL中使用if-then-else逻辑吗？
EN