sql-server-2005 - 用于用户定义的键/值对的连接表上的 Where 子句

Question

我们的应用程序允许管理员添加“用户属性”，以便他们能够定制系统以匹配他们自己的人力资源系统。例如，如果您的公司有部门，您可以在属性表中定义“部门”，然后添加与“部门”相对应的值，例如“珠宝”、“电子”等……然后您就可以将部门分配给用户.

这是架构:
(来源:mindgravy.net)

在此架构中，用户的每个属性只能有一个 UserPropertyValue，但不必具有该属性的值。

我正在尝试构建一个将在 SSRS 2005 中使用的查询，并让它使用 PropertyValues 作为用户的过滤器。我的查询如下所示:

SELECT UserLogin, FirstName, LastName
FROM Users U
LEFT OUTER JOIN UserPropertyValues UPV
    ON U.ID = UPV.UserID
WHERE UPV.PropertyValueID IN (1, 5)

当我运行它时，如果用户有任何属性值，它们就会被返回。我想要的是这个查询将返回具有 BY PROPERTY 值的用户。

因此，如果 PropertyValueID = 1 属于部门(珠宝)，而 PropertyValueID = 5 属于就业类型(全职)，我想返回部门珠宝中属于全职 EmployeeType 的所有用户，可以这样做吗？

这是一个完整的数据示例:

用户 A 有部门(珠宝值(value) = 1)和就业类型(全职值(value) = 5)

用户 B 有部门(电子值(value) = 2)和就业类型(全职值(value) = 5)

用户 C 有部门(珠宝值(value) = 1)和就业类型(兼职值(value) = 6)

我的查询应该只使用上述查询返回用户 A

更新:

我应该声明这个查询在 SSRS 中用作数据集，因此传递给查询的参数将是 @PropertyIDs，它在 SSRS 中被定义为多值参数。
WHERE UPV.PropertyValueID IN (@PropertyIDs)

Answer 1

我想出了如何让它以一种完全 hacky 的方式工作:

SELECT UserLogin, FirstName, LastName
FROM Users
LEFT OUTER JOIN 
(
    SELECT UserID
    FROM UserPropertyValues 
    WHERE PropertyValueID IN (@PropertyIDs)
    GROUP BY UserID
    HAVING COUNT(UserID) = 
        (
            SELECT COUNT(*) FROM 
            (
            SELECT PropertyID FROM PropertyValues 
            WHERE ID IN (@PropertyIDs) GROUP BY PropertyID
            ) p
        )   
) filtered on Users.UserID = filtered.UserID

因为我们可以确定参数@PropertyIDs 中使用的属性数量(由select count(*) 返回)，所以我们可以确保从查询返回的用户具有与@PropertyIDs 中传递的相同数量的属性。