SQL 条件选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1038435/
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
SQL conditional SELECT
提问by Karl
I would like to create a stored procedure with parameters that indicate which fields should be selected.
我想创建一个带有指示应选择哪些字段的参数的存储过程。
E.g. I would like to pass two parameters "selectField1" and "selectField2" each as bools.
例如，我想将两个参数“selectField1”和“selectField2”分别作为布尔值传递。
Then I want something like
然后我想要类似的东西
SELECT
if (selectField1 = true) Field1 ELSE do not select Field1
if (selectField2 = true) Field2 ELSE do not select Field2
FROM Table
Thanks Karl
谢谢卡尔
采纳答案by Justin Balvanz
Sounds like they want the ability to return only allowed fields, which means the number of fields returned also has to be dynamic. This will work with 2 variables. Anything more than that will be getting confusing.
听起来他们希望能够只返回允许的字段，这意味着返回的字段数量也必须是动态的。这将适用于 2 个变量。除此之外的任何事情都会变得混乱。
IF (selectField1 = true AND selectField2 = true)
BEGIN
SELECT Field1, Field2
FROM Table
END
ELSE IF (selectField1 = true)
BEGIN
SELECT Field1
FROM Table
END
ELSE IF (selectField2 = true)
BEGIN
SELECT Field2
FROM Table
END
Dynamic SQL will help with multiples. This examples is assuming atleast 1 column is true.
动态 SQL 将有助于倍数。此示例假设至少有 1 列为真。
DECLARE @sql varchar(MAX)
SET @sql = 'SELECT '
IF (selectField1 = true)
BEGIN
SET @sql = @sql + 'Field1, '
END
IF (selectField2 = true)
BEGIN
SET @sql = @sql + 'Field2, '
END
...
-- DROP ', '
@sql = SUBSTRING(@sql, 1, LEN(@sql)-2)
SET @sql = @sql + ' FROM Table'
EXEC(@sql)
回答by Quassnoi
In SQL, you do it this way:
在 中SQL，您可以这样做：
SELECT CASE WHEN @selectField1 = 1 THEN Field1 ELSE NULL END,
CASE WHEN @selectField2 = 1 THEN Field2 ELSE NULL END
FROM Table
Relational model does not imply dynamic field count.
关系模型并不意味着动态字段计数。
Instead, if you are not interested in a field value, you just select a NULLinstead and parse it on the client.
相反，如果您对字段值不感兴趣，您只需选择 aNULL并在客户端解析它。
回答by Welbog
You want the CASEstatement:
你想要这样的CASE声明：
SELECT
CASE
WHEN @SelectField1 = 1 THEN Field1
WHEN @SelectField2 = 1 THEN Field2
ELSE NULL
END AS NewField
FROM Table
EDIT: My example is for combining the two fields into one field, depending on the parameters supplied. It is a one-or-neithersolution (not both). If you want the possibility of having both fields in the output, use Quassnoi's solution.
编辑：我的示例是根据提供的参数将两个字段合并为一个字段。这是一个或两个的解决方案（不是两者兼而有之）。如果您希望在输出中同时包含两个字段，请使用 Quassnoi 的解决方案。
回答by l3a0
@selectField1 AS bit
@selectField2 AS bit
SELECT
CASE
WHEN @selectField1 THEN Field1
WHEN @selectField2 THEN Field2
ELSE someDefaultField
END
FROM Table
Is this what you're looking for?
这是你要找的吗？
回答by Daniel A. White
This is a psuedo way of doing it
这是一种伪方法
IF (selectField1 = true)
SELECT Field1 FROM Table
ELSE
SELECT Field2 FROM Table
回答by waqasahmed
what you want is:
你想要的是：
MY_FIELD=
case
when (selectField1 = 1) then Field1
else Field2
end,
in the select
在选择
However, y don't you just not show that column in your program?
但是，您不是在程序中不显示该列吗？
回答by Domingos
The noob way to do this:
这样做的菜鸟方法：
SELECT field1, field2 FROM table WHERE field1 = TRUE OR field2 = TRUE
You can manage this information properly at the programming language only doing an if-else.
您可以在编程语言中正确管理此信息，只需执行 if-else。
Example in ASP/JavaScript
ASP/JavaScript 中的示例
// Code to retrieve the ADODB.Recordset
if (rs("field1")) {
do_the_stuff_a();
}
if (rs("field2")) {
do_the_stuff_b();
}
rs.MoveNext();
