查询不排除具有特定权限的人

我有这样的数据:表 Person_by_Privileges:

<前>PersonID Last First 设施部门特权1 霍夫玛丽 S P abc1 霍夫玛丽 S P cde1 霍夫玛丽 SP def2 史密斯乔治 S P abc2 Smith Georg S P cde

我试图让查询只返回 Georg Smith，因为他没有 def 的权限.我在这样做时遇到了麻烦，因为它是不同的查询行.我正在尝试:

SELECT 不同的 [PersonID]，[最后的]，[第一的]，[设施]，[部门],权限=东西((SELECT ',' + pp.Privileages FROM Person_by_Privilages pp where Facility='S' FOR XML PATH ('')), 1, 1, '')来自 [Person_by_Privileges] pp在哪里设施='S'和(部门喜欢 ('%p%'))和不喜欢的权限 ('%def%')

但是当我查看该查询的第一个人 (Hoff) 时，不应返回该信息，因为他们确实拥有 def 的权限，我确实在结果中找到了该人:

PersonID Last First Facility Dept 特权1 Hoff Mary SP(她的特权列表附加在一起......确实包括def)

解决方案

您想显示用户行，前提是不存在具有定义权限的行.类似的东西

SELECT personid、last、first、facility、department、...FROM person_by_privilages ppWHERE 设施 = 'S'AND 部门喜欢 ('%P%')并且不存在(选择 *来自 person_by_privilages pp2哪里 pp2.personid = pp.personidAND pp2.facility = 'S'AND pp2.department like ('%P%')AND pp2.privilages LIKE ('%def%'));

I have data like this: table Person_by_Privileges:

PersonID Last  First Facility Department Privilages
1        Hoff  Mary  S        P          abc
1        Hoff  Mary  S        P          cde
1        Hoff  Mary  S        P          def
2        Smith Georg S        P          abc
2        Smith Georg S        P          cde

I'm trying to get the query to only return Georg Smith, since he doesn't have Privilege for def. I'm having trouble doing that, since it's different query lines. I'm trying this:

SELECT distinct [PersonID]
      ,[Last]
      ,[First]
      ,[Facility]
      ,[Department]
      ,Privilages = STUFF(
                 (SELECT ',' + pp.Privilages FROM Person_by_Privilages pp where Facility='S' FOR XML PATH ('')), 1, 1, ''
               ) 
  FROM [Person_by_Privilages] pp  
  where
  Facility='S'
  AND
  (
     Department like ('%p%')
  )
  and 
  Privilages NOT LIKE ('%def%')

But when I look at the first person (Hoff) for that query, that shouldn't be returned because they do have Privilage for def, I do get that person in the results:

PersonID Last First Facility Dept Privilages
1        Hoff Mary  S        P    (list of her Privilages appended together..does include the def)

解决方案

You want to show a users rows, provided there does not exist a row with a def privilege. Something like

SELECT personid, last, first, facility, department, ...
FROM person_by_privilages pp
WHERE facility = 'S'
AND department like ('%P%')
AND NOT EXISTS
(
  SELECT *
  FROM person_by_privilages pp2
  WHERE pp2.personid = pp.personid
  AND pp2.facility = 'S'
  AND pp2.department like ('%P%')
  AND pp2.privilages LIKE ('%def%')
);