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

本文介绍了动态名称/值对的T-SQL过滤的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I'll describe what I am trying to achieve:

我将带有名称值对的xml传递给SP，并将其放入表变量，例如 @nameValuePairs 。
我需要检索与ID /值对（属性，另一张表）完全匹配的表达式（表）的ID列表。

这是我的架构：

表达式表->（expressionId，attributeId）

Expressions table --> (expressionId, attributeId)

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

Attributes table --> (attributeId, attributeName, attributeValue)

在使用动态SQL和邪恶游标尝试了复杂的东西之后（有效，但速度很慢），这就是我现在得到的：

After trying complicated stuff with dynamic SQL and evil cursors (which works but it's painfully slow) this is what I've got now:

--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

请问人们是否可以查看并发现问题？

Can people please review and see if they can spot any problems? Is there a better way of doing this?

任何帮助表示赞赏！

推荐答案

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

I belive that this would satisfy the requirement you're trying to meet. I'm not sure how much prettier it is, but it should work and wouldn't require a temp table:

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

编辑：在进行了更多评估之后，我发现了一个问题，其中包括了某些不应该包含的表达式。我已修改查询以将其纳入帐户。

After doing some more evaluation, I found an issue where certain expressions would be included that shouldn't have been. I've modified my query to take that in to account.

这篇关于动态名称/值对的T-SQL过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

AttributeName