对动态名称/值对进行 T-SQL 过滤

Question

我将描述我想要实现的目标：

我将一个 xml 传递给 SP，其中包含我放入表变量中的名称值对，比方说 @nameValuePairs。 我需要检索与名称/值对（属性、另一个表）完全匹配的表达式（一个表）的 ID 列表。

这是我的架构：

表达式表 - -> (expressionId, attributeId)

属性表 --> （attributeId、attributeName、attributeValue）

在尝试使用动态 SQL 和邪恶游标进行复杂的操作（可以工作，但速度非常慢）之后，这就是我现在得到的：

--do the magic plz!

-- retrieve number of name-value pairs
SET @noOfAttributes = select count(*) from @nameValuePairs

select distinct
    e.expressionId, a.attributeName, a.attributeValue
into 
    #temp
from 
    expressions e
join
    attributes a
on
    e.attributeId = a.attributeId
join --> this join does the filtering
    @nameValuePairs nvp
on 
    a.attributeName = nvp.name and a.attributeValue = nvp.value
group by
    e.expressionId, a.attributeName, a.attributeValue

-- now select the IDs I need
-- since I did a select distinct above if the number of matches
-- for a given ID is the same as noOfAttributes then BINGO!
select distinct 
    expressionId
from 
    #temp
group by expressionId
having count(*) = @noOfAttributes 

人们可以检查一下，看看他们是否能发现任何问题吗？ 有更好的方法吗？

任何帮助表示赞赏！

Answer 1

我相信这会满足您想要满足的要求。 我不确定它有多漂亮，但它应该可以工作并且不需要临时表：

SET @noOfAttributes = select count(*) from @nameValuePairs

SELECT e.expressionid
FROM expression e
LEFT JOIN (
           SELECT attributeid
           FROM attributes a
           JOIN @nameValuePairs nvp ON nvp.name = a.Name AND nvp.Value = a.value 
           ) t ON t.attributeid = e.attributeid
GROUP BY e.expressionid
HAVING SUM(CASE WHEN t.attributeid IS NULL THEN (@noOfAttributes + 1) ELSE 1 END) = @noOfAttributes

编辑：在进行更多评估后，我发现一个问题，其中包含某些不应该包含的表达式。 我修改了我的查询以考虑到这一点。

Answer 2

我看到的一个错误是您没有别名为 b 的表，但您正在使用：a.attributeId = b.attributeId。

尝试修复它，看看它是否有效，除非我遗漏了一些东西。

编辑：我认为您刚刚在编辑中修复了此问题，但它应该是 a.attributeId = e.attributeId 吗？

Answer 3

这不是一个坏方法，具体取决于表的大小和索引，包括@nameValuePairs。 如果这些行计数很高或者变得很慢，您可能会更好地将 @namValuePairs 放入临时表中，添加适当的索引，并使用单个查询而不是两个单独的查询。

我确实注意到，您将不使用的列放入 #temp 中，排除它们会更快（尽管这意味着 #temp 中存在重复的行）。 此外，您的第二个查询在同一列上同时具有“不同”和“分组依据”。 你不需要两者，所以我会放弃“不同”（可能不会影响性能，因为优化器已经解决了这个问题）。

最后，#temp 在 expressionid 上使用聚集非唯一索引可能会更快（我假设这是 SQL 2005）。 您可以在 SELECT..INTO 之后添加它，但通常在加载之前添加它的速度一样快或更快。 这需要您首先 CREATE #temp，添加集群，然后使用 INSERT..SELECT 加载它。

我将添加一个在几分钟内合并查询的示例...好吧，这是将它们合并到单个查询中的一种方法（这也应该与 2000 兼容）：

-- retrieve number of name-value pairs
SET @noOfAttributes = select count(*) from @nameValuePairs

-- now select the IDs I need
-- since I did a select distinct above if the number of matches
-- for a given ID is the same as noOfAttributes then BINGO!
select
    expressionId
from 
    (
        select distinct
            e.expressionId, a.attributeName, a.attributeValue
        from 
            expressions e
        join
            attributes a
        on
            e.attributeId = a.attributeId
        join --> this join does the filtering
            @nameValuePairs nvp
        on 
            a.attributeName = nvp.name and a.attributeValue = nvp.value
    ) as Temp
group by expressionId
having count(*) = @noOfAttributes