none
sql 自联查询 RRS feed

  • 问题

  • 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
    ..

    2013年8月19日 14:10

答案

  • 有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.

    2013年8月20日 3:02
    版主

全部回复

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

    2013年8月20日 0:54
  • 有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.

    2013年8月20日 3:02
    版主