代码之家 › 专栏 › 技术社区 › Tommy Jakobsen

查询以筛选以逗号分隔字符串形式给出条件的表

sql-server-2005 sql

Tommy Jakobsen · 技术社区 · 14 年前

我需要一些帮助来创建查询。下面是一个示例表:

Segment   Name
1         Vincent
1         Jules
1         Lance
2         Vincent
3         Marsellus
4         Vincent
5         Marsellus

问题是筛选此表。要筛选的列是Segment列,要筛选的内容作为字符串参数给定,例如“1,2”,这意味着我需要同时位于Segment 1和Segment 2中的名称,并返回集合:

Segment   Name
1         Vincent
2         Vincent

怎么能做到?我们将非常感谢您的帮助。谢谢!

2 回复 | 直到 14 年前

gbn 14 年前

拆分字符串时,将得到值为1和2的行。然后,取匹配数=行数的行。

DECLARE @string varchar(8000)
SET @string = '1,2'

DECLARE @Mytable TABLE (Segment int, name varchar(8000))
INSERT @Mytable VALUES (1, 'Vincent')
INSERT @Mytable VALUES (1, 'Jules')
INSERT @Mytable VALUES (1, 'Lance')
INSERT @Mytable VALUES (2, 'Vincent')
INSERT @Mytable VALUES (3, 'Marsellus')
INSERT @Mytable VALUES (4, 'Vincent')
INSERT @Mytable VALUES (5, 'Marsellus')

;WITH Split AS
(
    SELECT SplitValue, COUNT(*) OVER() AS NumSplitValues
    FROM dbo.FN_ListToTable(',', @string)
)
SELECT
    [Name]
FROM
    @Mytable T
    JOIN
    SPLIT S ON T.Segment = S.SplitValue
GROUP BY
    [Name], S.NumSplitValues
HAVING
    COUNT(*) = S.NumSplitValues

如果你需要 Segment ,然后重新连接

;WITH Split AS
(
    SELECT SplitValue, COUNT(*) OVER() AS NumSplitValues
    FROM dbo.FN_ListToTable(',', @string)
), Matches AS
(
    SELECT
        [Name]
    FROM
        @Mytable T
        JOIN
        SPLIT S ON T.Segment = S.SplitValue
    GROUP BY
        [Name], S.NumSplitValues
    HAVING
        COUNT(*) = S.NumSplitValues
)
SELECT
    T.*
FROM
    Split S
    JOIN
    @Mytable T ON T.Segment = S.SplitValue
    JOIN
    Matches M ON T.[Name] = M.[Name]

split函数依赖于一个数字表,我从google中获取了它

CREATE FUNCTION dbo.FN_ListToTable (
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        SplitValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS SplitValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Number n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE SplitValue IS NOT NULL AND SplitValue <> ''
);
GO

Dave 14 年前

我通过创建一个将字符串解析为表的函数来解决这个问题。具体地说,我在web表单中有一个项目数组,我没有从表单中单独加载这些项目,而是选择将所有项目接受为一个逗号分隔的字符串。

/*********************************************************
** Parse A Comma Delimited String Into A Table
** Description: When A Web Page Sends An Array Of Data To
** The Server, The Array Is Comma Delimited.
** This Routine Returns All Of The Data From A Comma
** Delimited String Into A Table.
*********************************************************/
CREATE FUNCTION dbo.ParseByComma ( @String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
 VarSubString VARCHAR(10)
)
AS
BEGIN
    DECLARE @intPos INT,
            @SubStr VARCHAR(10)
    -- Remove All Spaces
    SET @String = REPLACE(@String, ' ','')
    -- Find The First Comma
    SET @IntPos = CHARINDEX(',', @String)
    -- Loop Until There Is Nothing Left Of @String
    WHILE @IntPos > 0
    BEGIN
        -- Extract The String
        SET @SubStr = SUBSTRING(@String, 0, @IntPos)
        -- Insert The String Into The Table
        INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
        -- Remove The String & Comma Separator From The Original
        SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
        -- Get The New Index To The String
        SET @IntPos = CHARINDEX(',', @String)
    END
    -- Return The Last One
    INSERT INTO @TblSubString (VarSubString) VALUES (@String)
    RETURN
END