問題描述

我有這樣的數據:表 Person_by_Privileges:

我試圖讓查詢只返回 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%')
);

這篇關于查詢不排除具有特定權限的人的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！