SQL取最底层的所有
类别ID 类别名称 上级类别ID
1 父类1 0
2 子类11 1
3 子类12 1
4 子子类111 2
5 父类2 0

取到结果

类别ID 类别名称 上级类别ID
3 子类12 1
4 子子类111 2
5 父类2 0

取到最下级的所有（有下级就不要上级 ）
[最优解释]

declare @t table( 类别ID int,  类别名称 varchar(20), 上级类别ID int)
insert into @t
select 1,'父类1'    ,0 union all
select 2,'子类11'   ,1 union all
select 3,'子类12'   ,1 union all
select 4,'子子类111',2 union all
select 5,'父类2'    ,0
select * from @t t1 
where not exists(select 1 from @t t2 where t1.类别ID=t2.上级类别ID)

[其他解释]

declare @t table( 类别ID int,  类别名称 nvarchar(20), 上级类别ID int)
insert into @t
select 1,'父类1'    ,0 
union all select 2,N'子类11'   ,1 
union all select 3,N'子类12'   ,1 
union all select 4,N'子子类111',2 
union all select 5,N'父类2'    ,0

select * from @t a
 where not exists(select 1 from @t where a.类别ID=上级类别ID)
 /*
 类别ID        类别名称                 上级类别ID
----------- -------------------- -----------
3           子类12                 1
4           子子类111               2
5           父类2                  0

(3 row(s) affected)

[其他解释]
select * from tb a where not exists(select 1 from tb b where a.类别ID=b.上级类别ID)也就是说不存在类别ID跟上级类别ID相等的行数据 能看明白吧
[其他解释]
null
[其他解释]
select * from tb a where not exists(select 1 from tb where 上级类别ID=a.类别ID)
[其他解释]
新版的code编辑真恶心
[其他解释]
MSSQL2005及以上版本：

CREATE TABLE t1 
 
(
id INT,
mingcheng VARCHAR(10),
pid INT
)
INSERT INTO t1
SELECT 1,'父类1',0 UNION ALL
SELECT 2,'子类11',1 UNION ALL
SELECT 3,'子类12',1 UNION ALL
SELECT 4,'子子类111',2 UNION ALL
SELECT 5,'父类2',0
SELECT * FROM t1

;WITH aaa AS
(
SELECT * FROM t1 WHERE pid=0
UNION ALL
SELECT a1.* FROM t1 AS a1 INNER JOIN aaa AS b1 ON a1.pid=b1.id
)
SELECT * FROM aaa WHERE pid>0

------------------
idmingchengpid
2子类111
3子类121
4子子类1112