我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
当前回答
有一个很好的、简单的、经过测试的方法:
/* Create table-value string: */
CREATE TYPE [String_List] AS TABLE ([Your_String_Element] varchar(max) PRIMARY KEY);
GO
/* Create procedure which takes this table as parameter: */
CREATE PROCEDURE [dbo].[usp_ListCheck]
@String_List_In [String_List] READONLY
AS
SELECT a.*
FROM [dbo].[Tags] a
JOIN @String_List_In b ON a.[Name] = b.[Your_String_Element];
我已经开始使用这种方法来修复实体框架的问题(对于我们的应用程序来说不够健壮)。所以我们决定给Dapper(和Stack一样)一个机会。还指定您的字符串列表为表与PK列修复您的执行计划很多。 这是一篇关于如何将一个表转换为Dapper的好文章——都是快速而干净的。
其他回答
我听到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子句的值。
这可能是一种有点讨厌的方法,我用过一次,相当有效。
根据你的目标,它可能会有用。
创建一个只有一列的临时表。 将每个查找值插入到该列中。 不使用IN,只需使用标准JOIN规则。(灵活性++)
这为您所能做的事情提供了一些额外的灵活性,但它更适合这样的情况:需要查询一个大型表,有良好的索引,并且希望多次使用参数化列表。节省了执行两次,所有的卫生工作都是手动完成的。
我从来没有时间去分析它到底有多快,但在我的情况下,它是需要的。
创建一个存储名称的临时表,然后使用以下查询:
select * from Tags
where Name in (select distinct name from temp)
order by Count desc
在我看来,正确的方法是将列表存储在字符串中(长度受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”必须返回与的最大行数相同的行数 我们可以在列表中找到的项目)
这里有另一种选择。只需将一个以逗号分隔的列表作为字符串参数传递给存储过程,然后:
CREATE PROCEDURE [dbo].[sp_myproc]
@UnitList varchar(MAX) = '1,2,3'
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))
函数:
CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(MAX)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
return
end