SQL 中的 IF ELSE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9870009/
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
IF ELSE Statement in SQL
提问by Cute Bear
SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE (IF S.Version = 0 THEN S.Version > 0 ELSE S.Version = @Version)
AND (IF S.SoftNo = 0 THEN S.SoftNo > 0 ELSE S.SoftNo = @SoftNo)
If Version is equal to zero, I want to list everything version number is greater then 0 if it is not equal to 0, then Version should be whatever it is value.
如果 Version 等于 0，我想列出所有版本号大于 0 如果它不等于 0，那么 Version 应该是它的值。
This is same for SoftNo.
这对于 SoftNo 也是一样的。
How can I fix my SQL query. It just doesn't work.
如何修复我的 SQL 查询。它只是不起作用。
More details:
更多细节：
This is what I want to achieve:
这就是我想要实现的目标：
if(Version == 0)
{
display every single rows if their version number is greater then 0;
}
else
{
Lets assume that Version is equal to 5. Then just display the rows if their Version number is equal to 5;
} // This is like a C# code of what I am trying to do in my sql query.
采纳答案by Cute Bear
Instead, I came up with this workaround,
相反，我想出了这个解决方法，
sSQL = @"SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE 1 = 1";
// 1=1 is used just to list everything.
if(pVersion != 0)
{
sSQL += " AND S.Version = @Version";
}
if(pSoftNo != 0)
{
sSQL += " AND S.SoftNo = @SoftNo";
}
Conclusion, if else part is moved to code side.
结论，if else 部分移到代码端。
回答by AbhimanyuAryan
Have you thought of using SQL cases?
你有没有想过使用SQL案例？
SELECT col1, col2,
CASE
WHEN expression THEN return
WHEN expression THEN return
ELSE return
END AS NameOfNewColWithReturnValues
FROM Col_FROM_WHICH_TABLE
回答by tobias86
Try something like this:
尝试这样的事情：
SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE ((@Version = 0 AND S.Version > 0) OR
(@Version <> 0 AND S.Version = @Version)) AND
((@SoftNo = 0 AND S.SoftNo > 0) OR
(@SoftNo <> 0 AND S.SoftNo = @SoftNo))
回答by Ian Yates
Why not do
为什么不做
SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE
(
(@Version = 0 OR (@Version <> 0 AND S.Version = @Version))
AND
(@SoftNo = 0 OR (@SoftNo <> 0 AND S.SoftNo = @SoftNo))
)
(Do you really need the NOLOCK?)
（你真的需要NOLOCK吗？）
回答by Daniel Powell
Don't use concatenated SQL, it is a poor habit that increases the probability of SQL injection vulnerabilities. Your SQL code is now the exact same as the following (safer) code:
不要使用串联的SQL，这是一个增加SQL注入漏洞概率的不良习惯。您的 SQL 代码现在与以下（更安全）代码完全相同：
SELECT
S.Id, S.Name, S.Version, S.SoftNo
FROM
SOFTWARE S WITH(NOLOCK)
WHERE
(@Version = 0 OR @Version = S.Version)
AND (@SoftNo = 0 OR @SoftNo = S.SoftNo)
回答by Paul Grimshaw
Don't quite understand, but if you mean the input variables equal zero, you could do something like:
不太明白，但如果您的意思是输入变量为零，您可以执行以下操作：
SELECT S.Id, S.Name, S.Version, S.SoftNo
FROM SOFTWARE S WITH(NOLOCK)
WHERE ((@Version = 0 AND S.Version > 0) OR (S.Version = @Version AND @Version > 0))
AND (@SoftNo = 0 AND S.SoftNo > 0) OR (@SoftNo = S.SoftNo AND @SoftNo > 0 )
