sql 自联查询

Question

ID USERID PARENTID USERNAME DEPT
GUID Sunny01 John01 张三 AD
GUID Max01   Sunny01 李四 AD
GUID Peter01 Sunny01 王五 AD
GUID Pony01  Sunny01 赵六 AD
GUID Anni01  Sunny01 孙七 AD
..

现在需要过滤掉UserID=Sunny01的记录。以上内容是根据部门查询出来的结果但是不需要显示领导。

结果：

ID USERID PARENTID USERNAME DEPT
GUID Max01   Sunny01 李四 AD
GUID Peter01 Sunny01 王五 AD
GUID Pony01  Sunny01 赵六 AD
GUID Anni01  Sunny01 孙七 AD
..

Answer 1

select ID ,USERID, PARENTID, USERNAME, DEPT  from table1 where USERID <> 'Sunny01'

Answer 2

有2种方法：

--One way
select t.ID,t.USERID,t.PARENTID,t.USERNAME,t.DEPT 
from TableName t 
where ID not in  (
select t1.id 
from TableName t1 inner join TableName t2 on t2.PARENTID=T1.USERID 
where t1.DEPT='AD'
) and t.DEPT='AD'
--The other way
select t.ID,t.USERID,t.PARENTID,t.USERNAME,t.DEPT 
from TableName t 
where not exists 
(
select 1 from TableName t1 where t1.DEPT=t.DEPT and t1.PARENTID=t.USERID
)

---

Caillen
<THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
Thanks
MSDN Community Support

Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.