使用另一个表过滤表格的SQL查询

Question

我目前有两个 SQL 表格，看起来像这样：

和...

我需要编写一个SELECT语句，从DataTable中检索包含与FilterTable匹配的行的所有产品。

所以根据上面的示例表，如果我运行查询，它将返回以下结果：

最近我发现了一个类似的问题： SQL查询，其中连接中的所有记录都符合条件？ 但是在实现类似功能方面一直不成功。

注意 - 我正在使用Microsoft SQL Server 2008。

- StevenP

Answer 1

这有点复杂，但是这里有一个解决方案。基本上，您需要检查数据表中有多少条记录与筛选表中的所有记录匹配。这使用子查询来完成：

SELECT *
FROM DataTable
WHERE ID IN (
  SELECT DT.ID
  FROM DataTable DT
    JOIN FilterTable FT ON FT.Name = DT.Name 
          AND FT.Value = DT.VALUE
  GROUP BY DT.ID
  HAVING COUNT(*) = (SELECT COUNT(*) FROM FilterTable)
)  

• SQL Fiddle演示

- sgeddes

---

这是一个不错的解决方案，不过我在想是否有一种不使用计数器的方法呢？ - StevenP

我很好奇。 "HAVING COUNT() = (SELECT COUNT() FROM FilterTable)" 如何指定并选择值1和4？Count用于计算表中行的数量。 - HelloWorld1

Answer 2

这样就可以了：

SELECT * FROM Data WHERE ID NOT IN (
    SELECT ID FROM Data JOIN Filter 
       on Data.Name = Filter.Name and Data.Value <> Filter.Value
)

我建立了一个 SQL Fiddle，如果你想尝试其他东西： http://sqlfiddle.com/#!3/38b87/6 编辑：

更好的答案：

SELECT *
FROM DATA
WHERE ID NOT IN (
  SELECT ID
  FROM DATA
  JOIN Filter ON DATA.Name = Filter.Name
    AND DATA.Value <> Filter.Value
) AND ID IN
(
  SELECT ID 
  FROM DATA 
  JOIN Filter ON DATA.Name = Filter.Name
)

现在可以适应至少一个匹配的筛选器，且没有不匹配的筛选器。

- Hotchips

---

这个应该改成 Data.Value = Filter.Value 吧？（因为你在使用 NOT IN） - AlexT82

然而，这将返回ID，其中ID没有与筛选器匹配的字段。（因此，如果您有一个新字段，比如“Active2”，并对其进行过滤，则将返回上面显示的所有ID。这是因为我们特别寻找筛选器不匹配的实例。） - Hotchips

不错的尝试，但这并不总是有效的--以这个为例：http://sqlfiddle.com/#!3/71703/1--话虽如此，我想知道你是否有更简单的方法... - sgeddes

@user2697139 不是的。这会查找所有过滤器不匹配的ID，然后将其过滤掉。当然，它返回了OP想要的结果。不幸的是，它也会返回任何一个ID中没有任何过滤器与之匹配的情况。不确定这是否是OP想要的。 - Hotchips

@Hotchips -- 是的，我已经玩了几分钟了。不确定如何避免使用 COUNT。无论如何，为你的努力点赞 :D - sgeddes

Answer 3

这里有一个使用几个 PIVOT 的选项

DECLARE @Data table ([ID] INT, [Name] VARCHAR(12), [Value] VARCHAR(2) )

DECLARE @Filter TABLE ( [Name] VARCHAR(12), [Value] VARCHAR(2)    )

    INSERT INTO @Data ([ID], [Name], [Value])
    VALUES   (1, 'productname', 'A')
            ,(1, 'cost', '20')
            ,(1, 'active', 'Y')
            ,(2, 'productname', 'A')
            ,(2, 'cost', '20')
            ,(2, 'active', 'N')
            ,(3, 'productname', 'B')
            ,(3, 'cost', '20')
            ,(3, 'active', 'Y')
            ,(4, 'productname', 'A') 
            ,(4, 'cost', '20')
            ,(4, 'active', 'Y')

    INSERT INTO @Filter ([Name], [Value])
    VALUES ('productname', 'A')
          ,('active', 'Y');

SELECT * 
FROM (  SELECT *
        FROM (select [ID], [Name], [value] from @Data) as s
        PIVOT 
        ( MAX([value]) FOR [name] in ( [productname], [active])
        ) as pvt) B
INNER JOIN 
        (   SELECT * 
        FROM (select [name], [value] from @Filter) as f
        PIVOT
        ( MAX([value]) for [Name] IN ([productname], [active]) 
        ) AS fpvt
    ) F 
ON F.active = b.active and f.productname = b.productname 

通过在DATA表和FILTER表上执行PIVOT操作，可以使它们对齐以进行内连接。这将返回在两个表中都匹配的记录。

- Steven

Answer 4

如果您正在使用存储过程，可以使用sp_executesql。

SET NOCOUNT ON
GO

    CREATE TABLE Data  
    (
         [ID] INT
        ,[Name] VARCHAR(12)
        ,[Value] VARCHAR(2)
    )

    CREATE TABLE Filter  
    (
         [Name] VARCHAR(12)
        ,[Value] VARCHAR(2)
    )

    INSERT INTO Data ([ID], [Name], [Value])
    VALUES   (1, 'productname', 'A')
            ,(1, 'cost', '20')
            ,(1, 'active', 'Y')
            ,(2, 'productname', 'A')
            ,(2, 'cost', '20')
            ,(2, 'active', 'N')
            ,(3, 'productname', 'B')
            ,(3, 'cost', '20')
            ,(3, 'active', 'Y')
            ,(4, 'productname', 'A') 
            ,(4, 'cost', '20')
            ,(4, 'active', 'Y')

    INSERT INTO Filter ([Name], [Value])
    VALUES ('productname', 'A')
          ,('active', 'Y')

    DECLARE @SQLColumns NVARCHAR(MAX) = SUBSTRING((SELECT DISTINCT ',[' +[Name]  +']' FROM Data FOR XML PATH('')),2,4000)
    DECLARE @SQLFilterColumns NVARCHAR(MAX) = SUBSTRING((SELECT 'AND [' +[Name]  +'] = ''' + [Value] + ''' ' FROM Filter FOR XML PATH('')),4,4000)

    DECLARE @SQLStatement NVARCHAR(MAX) = N'
    ;WITH DataSource ([ID]) AS
    (
        SELECT [ID]
        FROM
        (
            SELECT [ID]
                  ,[Name]
                  ,[Value]
            FROM Data
        ) DataSource
        PIVOT
        (
            MAX([Value]) FOR [Name] IN (' + @SQLColumns+  ')
        ) PVT
        WHERE ' +  @SQLFilterColumns + '
    )
    SELECT DT.[ID]
          ,DT.[Name]
          ,DT.[Value]
    FROM Data DT
    INNER JOIN DataSource DS
        ON DT.[ID] = DS.[ID]
    '

    EXECUTE sp_executesql @SQLStatement

    DROP TABLE Data
    DROP TABLE Filter

SET NOCOUNT OFF
GO

- gotqn