如何在 SQL SELECT 中执行 IF...THEN?
如何在 SQL SELECT
语句中执行 IF...THEN
?
例如:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
How do I perform an IF...THEN
in an SQL SELECT
statement?
For example:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(30)
看起来像一个老问题,但是如果我理解你的问题并正确思考,你想在 SQL 中实现 if/else 条件语句。 两者均作为单独的列进行计算。
iif()
函数于 2012 年引入,但让我们了解一下case
和iif()
语句之间的区别。 如果您只需要编写一个条件,那么iif()
就足够了,但是如果您想在一条语句中编写多个iif()
条件,那么您将不得不使用如上述回复所示的“案例”陈述。 这是一个示例供您参考。现在,如果您需要在上述语句中包含任何其他过滤器或条件,则需要编写另一个
iif()
语句。 另一方面,case
表达式可以在一个表达式中包含多个条件。 研究一下case
语句和case
表达式之间的区别。 下面是一个计算结果为值的case
表达式。Looks like an old question, however if I understood your question and thought correctly, you want to implement the if/else conditional statement in SQL. Both are calculated as a separate column.
The
iif()
function was introduced in 2012, however let's understand the difference betweencase
andiif()
statements. If you need to write just one condition, theniif()
is sufficient, however if you want to write multipleiif()
conditions within one statement then you will have to use `case' statement as demonstrated in above responses. Here is an example for your reference.Now if you need to include any other filter or condition in the above statement, you will need to write another
iif()
statement. On the other hand,case
expression can include multiple conditions within one expression. Do some research on the difference between acase
statement andcase
expression. The below is acase
expression that evaluates to a value.我喜欢使用 CASE 语句,但问题要求在 SQL Select 中使用 IF 语句。 我过去使用过的是:
它就像 Excel 或表格 IF 语句,其中有一个条件,后跟 true 条件,然后是 false 条件:
此外,您可以嵌套 if 语句(但是使用时应该使用 CASE :-)
(注意:这适用于 MySQL Workbench,但它可能无法在其他平台上运行)
I like the use of the CASE statements, but the question asked for an IF statement in the SQL Select. What I've used in the past has been:
It's like the Excel or sheets IF statements where there is a conditional followed by the true condition and then the false condition:
Furthermore, you can nest the if statements (but then use should use a CASE :-)
(Note: this works in MySQL Workbench, but it may not work on other platforms)
对于那些使用 SQL Server 2012 的用户来说,IIF 是一项已添加的功能,可作为 Case 语句的替代方案。
For those who uses SQL Server 2012, IIF is a feature that has been added and works as an alternative to Case statements.
问题:
ANSI:
使用别名(在本例中为
p
)将有助于防止出现问题。Question:
ANSI:
Using aliases --
p
in this case -- will help prevent issues.使用 SQL CASE 就像普通的 If / Else 语句一样。
在下面的查询中,如果过时值 = 'N' 或 InStock 值 = 'Y',则输出将为 1。否则输出将为 0。
然后我们将该 0 或 1 值放在可销售列下。
Using SQL CASE is just like normal If / Else statements.
In the below query, if obsolete value = 'N' or if InStock value = 'Y' then the output will be 1. Otherwise the output will be 0.
Then we put that 0 or 1 value under the Salable Column.
您可以有两种选择来实际实现:
使用 IIF,它是从 SQL Server 2012 引入的:
使用
Select Case
:<前><代码>选择案例
何时过时 = 'N' 或有货 = 'Y'
然后 1
其他 0
结束为可销售,*
来自产品
You can have two choices for this to actually implement:
Using IIF, which got introduced from SQL Server 2012:
Using
Select Case
:作为
CASE
语句的替代解决方案,可以使用表驱动方法:结果:
As an alternative solution to the
CASE
statement, a table-driven approach can be used:Result:
为了完整起见,我想补充一点,SQL 使用三值逻辑。 表达式:
可以产生三个不同的结果:
例如,如果产品已过时,但您不知道产品是否有库存,那么您不知道产品是否可销售。 您可以按如下方式编写这个三值逻辑:
一旦弄清楚它是如何工作的,您就可以通过决定 null 的行为将三个结果转换为两个结果。 例如,这会将 null 视为不可销售:
For the sake of completeness, I would add that SQL uses three-valued logic. The expression:
Could produce three distinct results:
So for example if a product is obsolete but you dont know if product is instock then you dont know if product is saleable. You can write this three-valued logic as follows:
Once you figure out how it works, you can convert three results to two results by deciding the behavior of null. E.g. this would treat null as not saleable:
它会是这样的:
It will be something like that:
有多种条件。
There are multiple conditions.
在这种情况下,case 语句是您的朋友,它采用以下两种形式之一:
简单 case:
扩展 case:
您甚至可以将 case 语句放在 order by 子句中,以实现真正奇特的排序。
The case statement is your friend in this situation, and takes one of two forms:
The simple case:
The extended case:
You can even put case statements in an order by clause for really fancy ordering.
如果您是第一次将结果插入表中,而不是将结果从一个表传输到另一个表,则这适用于 Oracle 11.2g:
If you're inserting results into a table for the first time, rather than transferring results from one table to another, this works in Oracle 11.2g:
SQL Server 中的简单 if-else 语句:
SQL Server 中的嵌套 If...else 语句 -
Simple if-else statement in SQL Server:
Nested If...else statement in SQL Server -
Microsoft SQL Server (T-SQL)
在
select
中,使用:在
where
子句中,使用:Microsoft SQL Server (T-SQL)
In a
select
, use:In a
where
clause, use:从 SQL Server 2012 开始,您可以使用 < code>IIF 函数 为此。
这实际上只是编写
CASE
的一种简写方式(尽管不是标准 SQL)。与扩展的
CASE
版本相比,我更喜欢简洁。IIF()
和CASE
都解析为 SQL 语句中的表达式,并且只能在明确定义的位置使用。如果这些限制无法满足您的需求(例如,需要根据某些条件返回不同形状的结果集),那么 SQL Server 也有一个过程
IF
关键字。但是,有时必须小心避免这种方法出现参数嗅探问题。
From SQL Server 2012 you can use the
IIF
function for this.This is effectively just a shorthand (albeit not standard SQL) way of writing
CASE
.I prefer the conciseness when compared with the expanded
CASE
version.Both
IIF()
andCASE
resolve as expressions within a SQL statement and can only be used in well-defined places.If your needs can not be satisfied by these limitations (for example, a need to return differently shaped result sets dependent on some condition) then SQL Server does also have a procedural
IF
keyword.Care must sometimes be taken to avoid parameter sniffing issues with this approach however.
使用 CASE 语句:
Use a CASE statement:
添加了一个新功能 IIF(我们可以简单地使用)在 SQL Server 2012 中:
A new feature, IIF (that we can simply use), was added in SQL Server 2012:
使用纯位逻辑:
请参阅工作演示:if then without
case
在 SQL Server 中。首先,您需要计算出所选条件的
true
和false
值。 这里有两个 NULLIF:组合在一起给出 1 或 0。接下来使用 位运算符。
这是最所见即所得方法。
Use pure bit logic:
See working demo: if then without
case
in SQL Server.For start, you need to work out the value of
true
andfalse
for selected conditions. Here comes two NULLIF:combined together gives 1 or 0. Next use bitwise operators.
It's the most WYSIWYG method.
这不是答案,只是我工作中使用的 CASE 语句的示例。 它有一个嵌套的 CASE 语句。 现在你知道为什么我的眼睛是斗鸡了吧。
This isn't an answer, just an example of a CASE statement in use where I work. It has a nested CASE statement. Now you know why my eyes are crossed.
来自 这个链接,我们可以理解T-SQL中的
IF THEN ELSE
:这对于T-SQL来说还不够好吗?
From this link, we can understand
IF THEN ELSE
in T-SQL:Isn't this good enough for T-SQL?
使用案例。 像这样的东西。
Use CASE. Something like this.
您可以在 SQL CASE 语句的力量,我认为您可以使用的语句将是这样的(来自 4guysfromrolla):
You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):
CASE
语句是 SQL 中最接近 IF 的语句,并且在所有版本的 SQL Server 上都受支持。如果您希望结果为布尔值,则只需使用
CAST
运算符。 如果您对int
感到满意,那么这是可行的:CASE
语句可以嵌入到其他CASE
语句中,甚至可以包含在聚合中。SQL Server Denali (SQL Server 2012) 添加了 IIF 声明也可在 access 中找到(由马丁·史密斯 ):
The
CASE
statement is the closest to IF in SQL and is supported on all versions of SQL Server.You only need to use the
CAST
operator if you want the result as a Boolean value. If you are happy with anint
, this works:CASE
statements can be embedded in otherCASE
statements and even included in aggregates.SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):