tables - 參數化SQL IN子句




w3c sql null (20)

Another possible solution is instead of passing a variable number of arguments to a stored procedure, pass a single string containing the names you're after, but make them unique by surrounding them with '<>'. Then use PATINDEX to find the names:

SELECT * 
FROM Tags 
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0

我如何使用可變數量的參數來對包含IN子句的查詢進行參數化,就像這樣?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

在這個查詢中,參數的數量可以在1到5之間。

我不想為這個(或XML)使用一個專用的存儲過程,但是如果有一些針對SQL Server 2008的優雅方式,我對此很開放。


For a variable number of arguments like this the only way I'm aware of is to either generate the SQL explicitly or do something that involves populating a temporary table with the items you want and joining against the temp table.


Here is another answer to this problem.

(新版本於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;
    }

乾杯。


Here's a technique that recreates a local table to be used in a query string. Doing it this way eliminates all parsing problems.

The string can be built in any language. In this example I used SQL since that was the original problem I was trying to solve. I needed a clean way to pass in table data on the fly in a string to be executed later.

Using a user defined type is optional. Creating the type is only created once and can be done ahead of time. Otherwise just add a full table type to the declaration in the string.

The general pattern is easy to extend and can be used for passing more complex tables.

-- Create a user defined type for the list.
CREATE TYPE [dbo].[StringList] AS TABLE(
    [StringValue] [nvarchar](max) NOT NULL
)

-- Create a sample list using the list table type.
DECLARE @list [dbo].[StringList]; 
INSERT INTO @list VALUES ('one'), ('two'), ('three'), ('four')

-- Build a string in which we recreate the list so we can pass it to exec
-- This can be done in any language since we're just building a string.
DECLARE @str nvarchar(max);
SET @str = 'DECLARE @list [dbo].[StringList]; INSERT INTO @list VALUES '

-- Add all the values we want to the string. This would be a loop in C++.
SELECT @str = @str + '(''' + StringValue + '''),' FROM @list

-- Remove the trailing comma so the query is valid sql.
SET @str = substring(@str, 1, len(@str)-1)

-- Add a select to test the string.
SET @str = @str + '; SELECT * FROM @list;'

-- Execute the string and see we've pass the table correctly.
EXEC(@str)

I think this is a case when a static query is just not the way to go. Dynamically build the list for your in clause, escape your single quotes, and dynamically build SQL. In this case you probably won't see much of a difference with any method due to the small list, but the most efficient method really is to send the SQL exactly as it is written in your post. I think it is a good habit to write it the most efficient way, rather than to do what makes the prettiest code, or consider it bad practice to dynamically build SQL.

I have seen the split functions take longer to execute than the query themselves in many cases where the parameters get large. A stored procedure with table valued parameters in SQL 2008 is the only other option I would consider, although this will probably be slower in your case. TVP will probably only be faster for large lists if you are searching on the primary key of the TVP, because SQL will build a temporary table for the list anyway (if the list is large). You won't know for sure unless you test it.

I have also seen stored procedures that had 500 parameters with default values of null, and having WHERE Column1 IN (@Param1, @Param2, @Param3, ..., @Param500). This caused SQL to build a temp table, do a sort/distinct, and then do a table scan instead of an index seek. That is essentially what you would be doing by parameterizing that query, although on a small enough scale that it won't make a noticeable difference. I highly recommend against having NULL in your IN lists, as if that gets changed to a NOT IN it will not act as intended. You could dynamically build the parameter list, but the only obvious thing that you would gain is that the objects would escape the single quotes for you. That approach is also slightly slower on the application end since the objects have to parse the query to find the parameters. It may or may not be faster on SQL, as parameterized queries call sp_prepare, sp_execute for as many times you execute the query, followed by sp_unprepare.

The reuse of execution plans for stored procedures or parameterized queries may give you a performance gain, but it will lock you in to one execution plan determined by the first query that is executed. That may be less than ideal for subsequent queries in many cases. In your case, reuse of execution plans will probably be a plus, but it might not make any difference at all as the example is a really simple query.

Cliffs notes:

For your case anything you do, be it parameterization with a fixed number of items in the list (null if not used), dynamically building the query with or without parameters, or using stored procedures with table valued parameters will not make much of a difference. However, my general recommendations are as follows:

Your case/simple queries with few parameters:

Dynamic SQL, maybe with parameters if testing shows better performance.

Queries with reusable execution plans, called multiple times by simply changing the parameters or if the query is complicated:

SQL with dynamic parameters.

Queries with large lists:

Stored procedure with table valued parameters. If the list can vary by a large amount use WITH RECOMPILE on the stored procedure, or simply use dynamic SQL without parameters to generate a new execution plan for each query.


I use a more concise version of the top voted answer :

List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();

var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));

It does loop through the tag parameters twice; but that doesn't matter most of the time (it won't be your bottleneck; if it is, unroll the loop).

If you're really interested in performance and don't want to iterate through the loop twice, here's a less beautiful version:

var parameters = new List<SqlParameter>();
var paramNames = new List<string>();
for (var i = 0; i < tags.Length; i++)  
{
    var paramName = "@tag" + i;

    //Include size and set value explicitly (not AddWithValue)
    //Because SQL Server may use an implicit conversion if it doesn't know
    //the actual size.
    var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; } 
    paramNames.Add(paramName);
    parameters.Add(p);
}

var inClause = string.Join(",", paramNames);

In ColdFusion we just do:

<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
    <cfquery name="q">
        select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
    </cfquery>

In SQL Server 2016+ another possibility is to use the OPENJSON function.

This approach is blogged about in OPENJSON - one of best ways to select rows by list of ids .

A full worked example below

CREATE TABLE dbo.Tags
  (
     Name  VARCHAR(50),
     Count INT
  )

INSERT INTO dbo.Tags
VALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)

GO

CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM   dbo.Tags T
WHERE  T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS
                  FROM   OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER  BY T.Count DESC

GO

EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'

DROP TABLE dbo.Tags 

Use the following stored procedure. It uses a custom split function, which can be found here .

 create stored procedure GetSearchMachingTagNames 
    @PipeDelimitedTagNames varchar(max), 
    @delimiter char(1) 
    as  
    begin
         select * from Tags 
         where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter) 
    end

原來的問題是“我如何參數化查詢...”

讓我在此陳述,這不是對原始問題的回答。 其他好的答案已經有一些示範。

就這樣說,繼續並標記這個答案,降低它的效果,將它標記為不是一個答案......做任何你認為正確的事情。

請參閱Mark Brackett的回答,以獲得我(和其他231人)贊成的首選答案。 在他的答案中給出的方法允許1)有效使用綁定變量,2)對於可靠的謂詞。

選定的答案

我想在這裡解決的是Joel Spolsky的回答中給出的方法,“選擇”的答案是正確的答案。

Joel Spolsky的方法很聰明。 它的工作原理是合理的,它將展現出可預測的行為和可預測的性能,給定“正常”值以及規範邊緣情況,如NULL和空字符串。 對於特定的應用程序可能就足夠了。

但是在概括這種方法的時候,我們還要考慮一些比較晦澀的角落案例,比如Name列中包含通配符(就像LIKE謂詞所認可的那樣)。我看到最常用的通配符是% (百分號)。 。 現在我們來處理這個問題,然後再討論其他案例。

%字符的一些問題

考慮名字值'pe%ter' 。 (對於這裡的示例,我使用文字字符串值代替列名稱。)具有“pe%ter”名稱值的行將通過以下格式的查詢返回:

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

但是,如果搜索條件的順序顛倒,那麼相同的行將不會返回:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

我們觀察到的行為有點奇怪。 更改列表中搜索項的順序會更改結果集。

無論他喜歡多少,我們可能不希望pe%ter匹配花生醬。

模糊的角落案例

(是的,我會同意這是一個不明確的情況,可能是一個不太可能被測試的情況,我們不希望在列值中使用通配符,我們可能會認為應用程序阻止存儲這樣的值。根據我的經驗,我很少看到一個數據庫約束,它特別禁止LIKE比較運算符右側的字符或模式被視為通配符。

修補一個洞

修補此漏洞的一種方法是轉義%通配符。 (對於不熟悉運算符上的escape子句的任何人,這裡有一個指向SQL Server文檔的鏈接。

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

現在我們可以匹配文字%了。 當然,當我們有一個列名時,我們需要動態地轉義通配符。 我們可以使用REPLACE函數來查找%字符的出現,並在每個字符的前面插入一個反斜杠字符,如下所示:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

這樣可以解決%通配符的問題。 幾乎。

逃避逃跑

我們承認我們的解決方案引入了另一個問題。 轉義字符。 我們看到,我們也需要逃避任何逃跑角色本身的事件。 這一次,我們使用! 作為轉義字符:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

下劃線也是

現在我們已經開始了,我們可以添加另一個REPLACE處理下劃線通配符。 為了好玩,這一次,我們將使用$作為轉義字符。

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

我更喜歡這種方法來逃避,因為它可以在Oracle和MySQL以及SQL Server中工作。 (我通常使用\ backslash作為轉義字符,因為這是我們在正則表達式中使用的字符,但為什麼會受到約定的限制!

那些討厭的括號

SQL Server還允許將通配符字符視為文字,方法是將它們括在括號[] 。 所以我們還沒有完成修復,至少對於SQL Server來說。 由於括號對有特殊的含義,我們也需要避開這些。 如果我們設法妥善地避開括號,那麼至少我們不必打開括號內的連字符和克拉。 我們可以在括號內留下任何%_字符,因為我們基本上禁用了方括號的特殊含義。

找到匹配的括號對應該不那麼困難。 這比處理singleton%和_的事件要困難得多。 (請注意,只是跳過括號中的所有事件是不夠的,因為單例括號被認為是一個文字,並且不需要轉義。邏輯變得比我能處理的模糊一點,而不需要運行更多的測試用例。)

內聯表達式變得混亂

SQL中的內聯表達式越來越長,越來越醜。 我們可以讓它工作,但天堂幫助那些落後的可憐人,並且必須破譯它。 儘管我很喜歡內聯表達式,但我傾向於不在這裡使用它,主要是因為我不想留下評論來解釋混亂的原因,並為此道歉。

一個函數在哪裡?

好的,如果我們不把它作為SQL中的內聯表達式來處理,那麼我們最接近的替代方案就是用戶定義的函數。 我們知道這不會加速任何事情(除非我們可以定義一個索引,就像我們可以使用Oracle一樣)。如果我們需要創建一個函數,那麼我們最好在調用SQL的代碼中這樣做聲明。

而且這個函數在行為上可能會有一些差異,這取決於DBMS和版本。 (對於Java開發人員如此熱衷於可以互換地使用任何數據庫引擎,大聲疾呼。)

領域知識

我們可能對列的領域有專業知識(也就是為該列強制執行的一組允許值)我們可能知道列中存儲的值永遠不會包含百分號,下劃線或括號在這種情況下,我們只包含一個快速評論,說明這些情況。

存儲在列中的值可以允許%或_字符,但是約束可能要求這些值被轉義,可能使用定義的字符,這樣LIKE比較“安全”。 同樣,關於允許的一組值的快速評論,特別是哪個字符被用作轉義字符,並且與Joel Spolsky的方法一致。

但是,如果缺乏專業知識和保證,對我們來說至少考慮處理那些晦澀難懂的案例非常重要,並考慮行為是否合理和“按照規範”。

其他問題重演

我相信其他人已經充分指出了其他一些通常考慮的關注領域:

  • SQL注入 (考慮用戶提供的信息,並將其包含在SQL文本中,而不是通過綁定變量提供)。使用綁定變量不是必需的,它只是阻止SQL注入的一種方便方法。處理方法:

  • 優化程序計劃使用索引掃描而不是索引查找,可能需要用於轉義通配符的表達式或函數(可能的表達式或函數索引)

  • 使用文字值代替綁定變量會影響可伸縮性

結論

我喜歡Joel Spolsky的方法。 這很聰明。 它的工作原理。

但是一看到它,我立即看到了它的一個潛在問題,讓它滑下來不是我的本性。 我不是要批評別人的努力。 我知道很多開發人員非常個人地工作,因為他們投入很多,他們非常關心它。 所以請理解,這不是個人攻擊。 我在這裡確定的是生產中出現問題的類型而不是測試。

是的,我已經遠離了原來的問題。 但是在哪裡可以留下這個筆記,關於我認為對於一個問題“選擇”的答案是一個重要的問題?


在我看來,解決這個問題的最佳來源是這個網站上發布的內容:

SYSCOMMENTS。 Dinakar Nethi

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

使用:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

信譽:Dinakar Nethi


如果你有SQL Server 2008或更高版本,我會使用表值參數

如果你不幸被困在SQL Server 2005上,你可以添加一個像這樣的CLR函數,

[SqlFunction(
    DataAccessKind.None,
    IsDeterministic = true,
    SystemDataAccess = SystemDataAccessKind.None,
    IsPrecise = true,
    FillRowMethodName = "SplitFillRow",
    TableDefinintion = "s NVARCHAR(MAX)"]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{
    if (s.IsNull)
        return new string[0];

    return s.ToString().Split(seperator.Buffer);
}

public static void SplitFillRow(object row, out SqlString s)
{
    s = new SqlString(row.ToString());
}

你可以像這樣使用,

declare @desiredTags nvarchar(MAX);
set @desiredTags = 'ruby,rails,scruffy,rubyonrails';

select * from Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc

對於SQL Server 2008,您可以使用表值參數 。 這是一項工作,但它可以說比我的其他方法更清潔。

首先,你必須創建一個類型

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然後,你的ADO.NET代碼如下所示:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

恕我直言,正確的方法是將列表存儲在字符串中(由DBMS支持的長度限制); 唯一的技巧是(為了簡化處理),我在字符串的開頭和結尾都有一個分隔符(在我的例子中是一個逗號)。 這個想法是“實時正常化”,將列表轉換為每列包含一行的單列表格。 這可以讓你轉

在(ct1,ct2,ct3 ... ctn)

變成一個

在(選擇...)

或者(我可能更喜歡的解決方案)定期加入,如果你只是添加一個“不同”來避免列表中重複值的問題。

不幸的是,分割字符串的技術是相當特定於產品的。 這裡是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”必須返回與我們在列表中找到的項目的最大數量一樣多的行)


您可以將該參數作為字符串傳遞

所以你有字符串

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

我們有一個函數可以創建一個你可以加入的表變量:

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT INTO @listTable
                        (Position,Value)
            VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

            SET @myPos = @myPos + 1

            IF Charindex(@delim, @list) = Len(@list)
              INSERT INTO @listTable
                          (Position,Value)
              VALUES     (@myPos,'')

            SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
        END

      IF Len(@list) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END 

所以:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

我會默認通過傳遞一個表值函數(從字符串返回一個表)到IN條件。

這裡是UDF的代碼(我從的某處獲得它,現在我找不到源代碼)

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

一旦你得到了這個,你的代碼將如此簡單:

select * from Tags 
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc

除非你有一個可笑的長字符串,這應該適用於表索引。

如果需要,您可以將其插入臨時表中,對其進行索引,然後運行連接...


我聽說Jeff / Joel今天在播客中談到了這個問題( 第34集 ,2008-12-16(MP3,31 MB),1小時03分38秒 - 1小時06分45秒),我想我已經召回了堆棧溢出正在使用LINQ to 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子句中。


這很糟糕,但如果你保證至少有一個,你可以這樣做:

SELECT ...
       ...
 WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

IN('tag1','tag2','tag1','tag1','tag1')將很容易被SQL Server優化。 另外,你會得到直接的索引


這是我用過的一種快速而骯髒的技術:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

所以這裡是C#代碼:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

兩個警告:

  • 表現很糟糕。 LIKE "%...%"查詢未編入索引。
  • 確保你沒有任何| ,空白或空標籤或這不起作用

還有其他方法可以實現這一點,有些人可能會認為這是更清潔的,所以請繼續閱讀。





parameters