代码之家 › 专栏 › 技术社区 › Saajid Ismail

如何让SQL函数返回WHERE子句中IN语句使用的列表?

where-clause function sql-server sql

Saajid Ismail · 技术社区 · 14 年前

我有一个复杂的SQL查询,需要进一步过滤。WHERE子句的一部分如下所示:

Where P.PeriodID in (36, 37)

Where P.PeriodID in dbo.GetPeriodsInRange(@startDate, @endDate)

上面的函数不需要为每行求值。对于每一行都是一样的,所以可能在执行查询之前可以进行一些优化。

我敢肯定,我打破了几个“最佳做法”在这里,所以请指出他们给我,如果有一个更好的方法来做到这一点。但是,性能不是问题,所以我愿意牺牲性能而支持简单性。

我的问题适用于T-SQL(MS SQL Server 2000/2005)

5 回复 | 直到 14 年前

DomreiRoam 14 年前

我的解决方案是按照Mudu的建议创建一个函数。我正在使用 this

从链接:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufn_GenerateIntegers] ( @MinValue INT, @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    WHILE @MinValue <= @MaxValue
    BEGIN
        INSERT INTO @Integers ( [IntValue] ) VALUES ( @MinValue )
        SET @MinValue = @MinValue + 1
    END

    RETURN
END
GO

use tempdb

select *into #test from 
(
    select 1 as n, 35 as periodId
    union 
    select 2 as n, 36 as periodId
    union 
    select 1 as n, 36 as periodId
    union 
    select 2 as n, 37 as periodId
) a

select p.* from #test   p
inner join  [dbo].[ufn_GenerateIntegers](36, 37) on [IntValue] = periodId

Borealid 14 年前

好吧,如果getPeriodsInRange实际上只是一个SQL查询,那么您可以使用嵌套查询而不是过程(您说过更简单!):

Where P.PeriodID in (Select PeriodID from MyDateTable WHERE pd > SomeMinValue AND pd < SomeMaxValue)

Matthias Meid 14 年前

我会编写一个表值函数,返回一个带句点的单列表。然后可以将这个表值结果进行内部联接。这样,它应该只执行一次,而不是每一行(但我不是绝对肯定这一点)。

进一步阅读: Table-Valued User-Defined Functions (MSDN)

干杯

onedaywhen 14 年前

首先,你不是“成套思考”。SQL语言只有一个数据结构,即表,即列的行。列的数据类型必须是标量才能满足第一个范式。所以没有数组、列表等。

你可以生成一个 PeriodID

考虑你的

Where P.PeriodID in (36, 37)

…可以重写为

Where P.PeriodID IN (
                     SELECT 36
                     UNION ALL
                     SELECT 37
                    )

…甚至

WHERE EXISTS (
              SELECT * 
                FROM (
                      SELECT 36
                      UNION ALL
                      SELECT 37
                     ) AS DT1 (PeriodID)
               WHERE P.PeriodID = DT1.PeriodID
              );

也就是说,退一步,这看起来像是一个情况下,你更好的工作与自然关键时期,是化合物 (StartDate, EndDate) ,而不是人工/代理键

SELECT P1.PeriodID
  FROM Periods AS P1
 WHERE CASE 
          WHEN @StartDate > P1.StartDate THEN @StartDate 
          ELSE P1.StartDate
       END 
       <= 
       CASE 
          WHEN @EndDate > P1.EndDate THEN P1.EndDate 
          ELSE @EndDate 
       END;

barrylloyd 14 年前

不管你在GetPeriodsInRange函数中做什么-你能不能把它拉到主查询中,比如。。。

Where P.PeriodID in
(
 select PeriodID from...etc
)

如果函数正在做更复杂的事情,这就行不通了!也许你能让我们看看函数里有什么?