我如何参数化一个包含有可变数量参数的IN子句的查询，就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中，参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML)，但如果有一些特定于SQL Server 2008的优雅方式，我愿意接受。
当前回答
在我看来，正确的方法是将列表存储在字符串中(长度受DBMS支持的限制);唯一的技巧是(为了简化处理)我在字符串的开头和结尾都有一个分隔符(在我的例子中是一个逗号)。其思想是“动态规范化”，将列表转换为单列表，每个值包含一行。这让你可以转弯
In (ct1,ct2, ct3…卡通)
成一个
在(选择…)
或者(我可能更喜欢的解决方案)常规连接，如果你只是添加一个“distinct”来避免列表中重复值的问题。
不幸的是，分割字符串的技术是相当特定于产品的。 下面是SQL Server的版本:
with qry(n, names) as
(select len(list.names) - len(replace(list.names, ',', '')) - 1 as n,
substring(list.names, 2, len(list.names)) as names
from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' names) as list
union all
select (n - 1) as n,
substring(names, 1 + charindex(',', names), len(names)) as names
from qry
where n > 1)
select n, substring(names, 1, charindex(',', names) - 1) dwarf
from qry;
Oracle版本:
select n, substr(name, 1, instr(name, ',') - 1) dwarf
from (select n,
substr(val, 1 + instr(val, ',', 1, n)) name
from (select rownum as n,
list.val
from (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val
from dual) list
connect by level < length(list.val) -
length(replace(list.val, ',', ''))));
MySQL版本:
select pivot.n,
substring_index(substring_index(list.val, ',', 1 + pivot.n), ',', -1) from (select 1 as n
union all
select 2 as n
union all
select 3 as n
union all
select 4 as n
union all
select 5 as n
union all
select 6 as n
union all
select 7 as n
union all
select 8 as n
union all
select 9 as n
union all
select 10 as n) pivot, (select ',Doc,Grumpy,Happy,Sneezy,Bashful,Sleepy,Dopey,' val) as list where pivot.n < length(list.val) -
length(replace(list.val, ',', ''));
(当然，“pivot”必须返回与的最大行数相同的行数 我们可以在列表中找到的项目)
其他回答
如果在IN子句中存储了以逗号(，)分隔的字符串，则可以使用charindex函数获取值。如果使用. net，则可以使用SqlParameters进行映射。
DDL Script:
CREATE TABLE Tags
([ID] int, [Name] varchar(20))
;
INSERT INTO Tags
([ID], [Name])
VALUES
(1, 'ruby'),
(2, 'rails'),
(3, 'scruffy'),
(4, 'rubyonrails')
;
t - sql:
DECLARE @Param nvarchar(max)
SET @Param = 'ruby,rails,scruffy,rubyonrails'
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
您可以在. net代码中使用上述语句并将参数映射为SqlParameter。
提琴手演示
编辑: 使用下面的脚本创建名为SelectedTags的表。
DDL Script:
Create table SelectedTags
(Name nvarchar(20));
INSERT INTO SelectedTags values ('ruby'),('rails')
t - sql:
DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st
SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0
我听到Jeff/Joel今天在播客上谈论这个(第34集，2008-12-16 (MP3, 31 MB)， 1小时03分38秒-1小时06分45秒)，我想我记得Stack Overflow使用LINQ到SQL，但也许它被抛弃了。在LINQ to SQL中也是如此。
var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };
var results = from tag in Tags
where inValues.Contains(tag.Name)
select tag;
就是这样。而且，是的，LINQ已经足够向后看了，但是Contains子句对我来说似乎太向后看了。当我不得不在工作中为一个项目做类似的查询时，我自然地试图用错误的方式来做这个，在本地数组和SQL Server表之间做一个连接，认为LINQ to SQL翻译器将足够聪明，以某种方式处理翻译。它没有这样做，但它确实提供了一个描述性的错误消息，并指示我使用Contains。
无论如何，如果您在强烈推荐的LINQPad中运行此命令，并运行此查询，您可以查看SQL LINQ提供程序生成的实际SQL。它将向您显示每个参数化为IN子句的值。
可以将参数作为字符串传递
这是弦
DECLARE @tags
SET @tags = ‘ruby|rails|scruffy|rubyonrails’
select * from Tags
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc
然后你所要做的就是将字符串作为1参数传递。
这是我使用的分裂函数。
CREATE FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
这是这个问题的另一个答案。
(新版本发布于6/4/13)。
private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
{
var ds = new DataSet();
using (var sqlConn = new SqlConnection(scsb.ConnectionString))
{
var sqlParameters = new List<SqlParameter>();
var replacementStrings = new Dictionary<string, string>();
if (pars != null)
{
for (int i = 0; i < pars.Length; i++)
{
if (pars[i] is IEnumerable<object>)
{
List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());
}
else
{
sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));
}
}
}
strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));
using (var sqlCommand = new SqlCommand(strSql, sqlConn))
{
if (pars != null)
{
sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
}
else
{
//Fail-safe, just in case a user intends to pass a single null parameter
sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
}
using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(ds);
}
}
}
return ds;
}
欢呼。
在SQL SERVER 2016或更高版本中，您可以使用STRING_SPLIT。
DECLARE @InParaSeprated VARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails'
DECLARE @Delimeter VARCHAR(10) = ','
SELECT
*
FROM
Tags T
INNER JOIN STRING_SPLIT(@InputParameters,@Delimeter) SS ON T.Name = SS.value
ORDER BY
Count DESC
我使用这个是因为在我的查询中，有时连接速度比Like操作符快。 此外，您还可以按您喜欢的任何单独格式输入无限数量的数据。 我喜欢这个。
推荐文章
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
- 如何改变字符集(和排序)在整个数据库?
- mySQL:: insert到表，数据从另一个表?
- nvarchar(max)非文本
- 如何在Postgres中获得两个字段的MIN() ?
- 在Swift中如何调用GCD主线程上的参数方法?
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- SQL在Oracle中连接多行列值的查询
