带有 IN 子句值列表的 TSQL 变量 [英] TSQL Variable With List of Values for IN Clause

问题描述

我想在 select 语句中使用类似于CASE WHEN ... THEN 1 ELSE 0 END"的子句.棘手的部分是我需要它与value IN @List"一起使用.

I want to use a clause along the lines of "CASE WHEN ... THEN 1 ELSE 0 END" in a select statement. The tricky part is that I need it to work with "value IN @List".

如果我对列表进行硬编码，它可以正常工作 - 并且性能良好:

If I hard code the list it works fine - and it performs well:

SELECT
       CASE WHEN t.column_a IN ( 'value a', 'value b' ) THEN 1 ELSE 0 END AS priority
      , t.column_b
      , t.column_c
  FROM
       table AS t
 ORDER BY
       priority DESC

我想做的是:

-- @AvailableValues would be a list (array) of strings.
DECLARE
        @AvailableValues ???

 SELECT
        @AvailableValues = ???
   FROM
        lookup_table

 SELECT
        CASE WHEN t.column_a IN @AvailableValues THEN 1 ELSE 0 END AS priority
      , t.column_b
      , t.column_c
   FROM
        table AS t
  ORDER BY
        priority DESC

不幸的是，SQL Server 似乎没有这样做 - 您不能使用带有 IN 子句的变量.所以这给我留下了一些其他选择:

Unfortunately, it seems that SQL Server doesn't do this - you can't use a variable with an IN clause. So this leaves me with some other options:

1. 将@AvailableValues"设为逗号分隔的字符串并使用 LIKE 语句.这表现不佳.
2. 对lookup_table"使用内联 SELECT 语句代替变量.同样，性能不佳(我认为)，因为它必须在每一行上查找表.
3. 编写一个环绕 SELECT 语句的函数来代替变量.我还没有尝试过(现在会尝试)，但它似乎与直接 SELECT 语句有同样的问题.
4. ???

还有其他选择吗?性能对于查询非常很重要 - 它必须非常快，因为它为网站提供实时搜索结果页面(即无缓存).

Are there any other options? Performance is very important for the query - it has to be really fast as it feeds a real-time search result page (i.e. no caching) for a web site.

这里还有其他选择吗?有没有办法提高上述选项之一的性能以获得良好的性能?

Are there any other options here? Is there a way to improve the performance of one of the above options to get good performance?

预先感谢您提供的任何帮助！

Thanks in advance for any help given!

更新:我应该提到上面例子中的lookup_table"已经是一个表变量.我还更新了示例查询，以更好地演示我如何使用该子句.

UPDATE: I should have mentioned that the 'lookup_table' in the example above is already a table variable. I've also updated the sample queries to better demonstrate how I'm using the clause.

更新 II:我突然想到 IN 子句正在操作 NVARCHAR/NCHAR 字段(由于历史表设计原因).如果我要进行处理整数字段的更改(即通过 PK/FK 关系约束)，这会对性能产生很大影响吗?

UPDATE II: It occurred to me that the IN clause is operating off an NVARCHAR/NCHAR field (due to historical table design reasons). If I was to make changes that dealt with integer fields (i.e through PK/FK relationship constraints) could this have much impact on performance?

推荐答案

您可以在 IN 子句中使用变量，但不能以您想要的方式使用.例如，您可以这样做:

You can use a variable in an IN clause, but not in the way you're trying to do. For instance, you could do this:

declare @i int
declare @j int

select @i = 10, @j = 20

select * from YourTable where SomeColumn IN (@i, @j)

关键是变量不能代表多个值.

The key is that the variables cannot represent more than one value.

要回答您的问题，请使用内联选择.只要您不在查询中引用外部值(这可能会更改每行的结果)，引擎就不会从表中重复选择相同的数据.

To answer your question, use the inline select. As long as you don't reference an outer value in the query (which could change the results on a per-row basis), the engine will not repeatedly select the same data from the table.

这篇关于带有 IN 子句值列表的 TSQL 变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！