当与内部连接一起使用时，填充函数返回重复的记录

Question

你好，我想从责任表中获取用户责任列表，并从用户表中匹配user_id，但是当我在这些表上应用内部连接时，我得到了重复的记录。

用户表

​

责任表

​

​

下面是查询。

SELECT DISTINCT u.[user_id], 
                u.first_name, 
                u.mobile, 
                responsibility = Stuff((SELECT ',' + responsibility_nm 
                                        FROM   dms_responsibility AS dr 
                                        WHERE  responsibility_id = 
                                               dur.responsibility_id 
                                        FOR xml path('')), 1, 1, '') 
FROM   dms_user_responsibilities AS dur 
       INNER JOIN dms_user AS u 
               ON u.[user_id] = dur.[user_id] 
                  AND u.territory_cd = dur.territory_cd 
GROUP  BY u.[user_id], 
          u.first_name, 
          dur.responsibility_id, 
          u.mobile 

​

​

此查询返回重复。但是，如果我尝试在没有内部联接的情况下获取责任，那么我会得到正确的结果。

select STUFF((select ',' + responsibility_nm from DMS_Responsibility 
where responsibility_id in (1,2) FOR XML PATH('')), 1, 1, '') as res

​

​

我搞不懂是什么导致了这个问题。

Answer 1

最后在我的查询中找到了解决方案和解决方案。我所做的是从DMS_User_Responsibilities中获取单个特定记录的所有记录，这就是为什么它从DMS_Responsibilities表中返回所有职责的原因。

我的查询修复方法是。

select distinct u.[user_id], u.first_name + ' ' + u.Name + ' (' + 
convert(varchar(10), u.[user_id]) + ')' as Name, u.mobile,
u.email, u.start_dt, u.end_dt,
responsibility = STUFF((select distinct ',' + responsibility_nm from 
DMS_Responsibility as dr
where responsibility_id in (
select dr.responsibility_id from DMS_Responsibility as dr
inner join DMS_user_Responsibilities as dur
on dur.Responsibility_id = dr.Responsibility_id
where dur.[User_id] = u.[user_id] and dur.territory_cd = u.territory_cd -- This is the fix which i did, now its getting only the particular userid for the record.
) FOR XML PATH('')), 1, 1, '')
from dms_user_responsibilities as dur 
inner join dms_user as u
on u.[user_id] = dur.[user_id] and u.territory_cd = dur.territory_cd

​

​

Answer 2

当您为XML生成时，您的users和resposibilities之间没有关系。尝试在构建XML期间添加关系。

SELECT DISTINCT u.[user_id], 
                u.first_name, 
                u.mobile, 
                responsibility = Stuff ((SELECT ',' + responsibility_nm 
                                         FROM   dms_responsibility AS dr 
                                 INNER JOIN dms_user_responsibilities t1 
                                         ON dr.responsibility_id = 
                                            t1.responsibility_id 
                                 INNER JOIN dms_user t2 
                                         ON t2.[user_id] = t1.[user_id] 
                                            AND t2.territory_cd = 
                                                t1.territory_cd 
                                         WHERE  t1.responsibility_id = 
                                                dur.responsibility_id 
                                         FOR xml path('')), 1, 1, '') 
FROM   dms_user_responsibilities AS dur 
       INNER JOIN dms_user AS u 
               ON u.[user_id] = dur.[user_id] 
                  AND u.territory_cd = dur.territory_cd