sql自联查询
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的记录。以上内容是根据部门查询出来的结果但是不需要显示领导。
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [ID] varchar(100), [USERID] varchar(100), [PARENTID] varchar(100), [USERNAME] varchar(100), [DEPT] varchar(100));
insert #temp
select 'GUID','Sunny01','John01','张三','AD' union all
select 'GUID','Max01','Sunny01','李四','AD' union all
select 'GUID','Peter01','Sunny01','王五','AD' union all
select 'GUID','Pony01','Sunny01','赵六','AD' union all
select 'GUID','Anni01','Sunny01','孙七','AD'
--SQL:
--查出所有有领导的人.如果领导的层级有多层,且不固定,请楼主给出具体需求.中间层的领导是否显示?
select * from #temp a
WHERE EXISTS
(
SELECT 1
FROM #temp b
WHERE a.PARENTID = b.USERID
)
/*
IDUSERIDPARENTIDUSERNAMEDEPT
GUIDMax01Sunny01李四AD
GUIDPeter01Sunny01王五AD
GUIDPony01Sunny01赵六AD
GUIDAnni01Sunny01孙七AD
*/