sql - 基于多对多过滤器进行选择

Question

晚上好

我有一个类似于下图的表格布局。 User 表存储了我的大部分信息。对于每个用户，我还可以有数量不确定的Preference 和Location 选择，我将它们存储在联结表中，如图所示。

1. 当管理员开始生成报告时，他们会像过滤器一样使用首选项和位置选择。例如，他们可以选择 Preference1 和 Preference3，以及 UserLocation2 和 UserLocation4，并且报告应该只显示至少具有一个偏好和至少一个位置的用户。是否可以在一个 SQL select 语句中执行此操作？我正在使用 SQL Server 2008 R2。

2. 假设用户记录符合上述要求，有没有办法显示他们所有的偏好和位置，并连接到一个新列中。我已经想出如何为单个用户返回连接结果，但想知道它是否也可以作为同一 SQL select 语句的一部分来完成。

这就是我为单个 UserID 返回以逗号分隔的首选项...

DECLARE @concatlist VARCHAR(MAX)
SELECT @concatlist = COALESCE(@concatlist+', ', '') + dbo.Preference.Title
FROM dbo.UserPreference
LEFT OUTER JOIN dbo.Preference 
ON dbo.UserPreference.PreferenceID = dbo.Preference.PreferenceID
WHERE dbo.UserPreference.UserID = 5
SELECT @concatlist as OutputColumn

任何指针都会很棒。我在这上面花了半天时间，虽然我可以部分做到，但找不到在一条语句中做到这一点的方法。

谢谢，

Answer 1

你可以试试这个

WITH UPreference AS (
    SELECT  u.*,p.*
    FROM    Users u
    INNER JOIN UserPreference up
      ON u.UserID = up.UserID
    INNER JOIN Preference p
      ON up.PreferenceID = p.PreferenceID
) 
, ULocation AS   (
    SELECT  u.*,l.*
    FROM    Users u
    INNER JOIN UserLocation ul
      ON u.UserID = ul.UserID
    INNER JOIN Location l
      ON l.LocationID = ul.LocationID
)
SELECT  u.*
    ,AllPreferences = STUFF((SELECT ',' + up.Title FROM UPreference up WHERE up.UserID = u.UserID FOR XML PATH('')) ,1,1,'')
    ,AllLocations = STUFF((SELECT ',' + ul.[Name] FROM ULocation ul WHERE ul.UserID = u.UserID FOR XML PATH('')) ,1,1,'')
FROM    Users u 
WHERE 
  EXISTS(SELECT 1 FROM UPreference up WHERE up.UserID = u.UserID AND up.Title IN ('Preference1','Preference3')
AND 
  EXISTS(SELECT 1 FROM ULocation ul WHERE ul.UserID = u.UserID AND ul.[Name] IN ('Location2','Location4')