SQL Int 数组和 IN 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13508617/
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
Int array and IN statement
提问by Jacek
How can I declare int array in SQL Server?
如何在 SQL Server 中声明 int 数组？
After fill it, I would like to use it in IN statement to check, how can I change my code?
填完后，想在IN语句中用它来检查，我该如何更改我的代码？
declare @mode int =1
declare @acceptedFormTypeIds table (id int)
case @mode
when 1 then
insert into @acceptedFormTypeIds(id) values (1, 2, 3, 4)
when 2 then
insert into @acceptedFormTypeIds(id) values (1, 3)
when 3 then
insert into @acceptedFormTypeIds(id) values (2, 4)
else
insert into @acceptedFormTypeIds(id) values (1, 2, 3, 4)
end
...
WHERE
tFRM.SendDate between @datefrom and @dateto
and tFRM.TemplateId IN @acceptedFormTypeIds.id
回答by podiluska
In databases, don't ever think of arrays. Think of sets, or tables.
在数据库中，永远不要考虑数组。想想集合或表格。
The structure of the insert statement should be
插入语句的结构应该是
insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)
You can use if
您可以使用 if
if @mode=2
begin
insert into @acceptedFormTypeIds(id) values (1), (3)
end
else
begin
if @mode=3
begin
insert into @acceptedFormTypeIds(id) values (2), (4)
end
else
begin
insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)
end
end
However, it may be worth having a FormTypeModeAcceptedtable depending on your usage.
但是，FormTypeModeAccepted根据您的使用情况，可能值得拥有一张桌子。
回答by Guffa
There is no switch-like statement, the caseis an expression, so you would use if.
没有类似 switch 的语句，它case是一个表达式，因此您将使用if.
To insert multiple records, you use values (2), (4)instead of values (2, 4).
要插入多条记录，请使用values (2), (4)代替values (2, 4)。
When using the table in in, you need to select from it.
使用中的表时in，需要从中进行选择。
declare @mode int = 1
declare @acceptedFormTypeIds table (id int)
if (@mode = 1) begin
insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)
end else if (@mode = 2) begin
insert into @acceptedFormTypeIds(id) values (1), (3)
end else if (@mode = 3) begin
insert into @acceptedFormTypeIds(id) values (2), (4)
end else begin
insert into @acceptedFormTypeIds(id) values (1), (2), (3), (4)
end
...
WHERE
tFRM.SendDate between @datefrom and @dateto
and tFRM.TemplateId IN (select id from @acceptedFormTypeIds)
回答by GarethD
To switch statements you need to use IFrather than CASE, you also need to put each value to insert in it's own parentheses.
要使用IF而不是使用 switch 语句CASE，您还需要将每个值插入到它自己的括号中。
e.g.
例如
DECLARE @mode INT = 1;
DECLARE @acceptedFormTypeIds TABLE (id INT);
IF @mode = 1
INSERT @acceptedFormTypeIds (id) VALUES (1), (2), (3), (4);
ELSE IF @mode = 2
INSERT @acceptedFormTypeIds (id) VALUES (1), (3);
ELSE IF @mode = 2
INSERT @acceptedFormTypeIds (id) VALUES (2), (4);
ELSE
INSERT @acceptedFormTypeIds (id) VALUES (1), (2), (3), (4);
SELECT *
FROM @acceptedFormTypeIds;
