选择查询中使用的逗号分隔字段中的行数据。

Question

我正在尝试构建一个选择查询，它使用不在Table1中的ExcludedList of Table2返回产品类型描述列表。

我在Server 2008中有以下表和数据。

Table1(nProdType INT, sProdDesc VARCHAR)

nProdType   SProdDesc
----------- --------------------
1           Pencils
2           Paper
3           Pens
4           Markers
5           Erasers
6           Crayons
7           HighLighters
8           Rulers


Table2(ClassID INT, ExcludeList VARCHAR)

ClassID     ExcludedList
----------- --------------------
100         2,3,4,8
101         1,2,5,6,7,8
102         4,5,6,7
103         1,2,3,4,5,6,7,8
104         7

查询应返回以下内容：

ClassID  nProdType  sProdDesc
-------- ---------  --------------
100      1          Pencils
100      5          Erasers
100      6          Crayons
100      7          HighLighters
101      3          Pens
101      4          Markers
102      1          Pencils
102      2          Paper
102      3          Pens
102      8          Rulers

..and等等..。

我知道如何构建一个用于拆分逗号分隔字段的函数(当然还有很多解决方案)，但是它们返回表中的所有行，我希望它是每个记录(ClassID)，这样我就可以查询ExcludedList中没有的行了。我试图不在C#中对其进行编码，也不使用RecordSet。

Answer 1

您可以使用CSV拆分器。以下是Jeff的DelimitedSplit8K函数。

;WITH CteDelimitted AS(
    SELECT
        t.ClassID,
        nProdType = CAST(s.Item AS INT)
    FROM Table2 t
    CROSS APPLY dbo.DelimitedSplit8K(t.ExcludedList, ',') s
),
CteCross AS(
    SELECT
        t2.ClassID,
        t1.nProdType,
        t1.SprodDesc
    FROM Table1 t1
    CROSS JOIN(
        SELECT DISTINCT ClassID FROM Table2
    )t2

)
SELECT * 
FROM CteCross c
WHERE NOT EXISTS(
    SELECT 1
    FROM CteDelimitted
    WHERE
        ClassID = c.ClassID
        AND nProdType = c.nProdType
)
ORDER BY ClassID, nProdType

SQL Fiddle

另一种使用NOT IN的方法

WITH Cte AS(
    SELECT
        t2.ClassID,
        t1.nProdType,
        t1.SprodDesc
    FROM Table1 t1
    CROSS JOIN(
        SELECT DISTINCT ClassID FROM Table2
    )t2
)
SELECT *
FROM Cte c
WHERE c.nProdType NOT IN(
    SELECT CAST(s.Item AS INT)
    FROM Table2
    CROSS APPLY dbo.DelimitedSplit8K(ExcludedList, ',') s
    WHERE ClassID = c.ClassID
)
ORDER BY ClassID, nProdType

SQL Fiddle