求查询sql。。急。。。

[fengyuananan]

select * from dbo.Forbug_CRMH 这样的一句sql 查询来的 结果是：
ht_kmno ht_kmname stano
001001 交通费 1
001001001 公交费 1
001001001001 公交费01 1
001001002 打的费 1
001002 住宿费 1
001002001 小单间 1
001002002 大间 1
001002002001 大间01 1
001002003 房间
001002003001 房间01 1
001002003004 房间02 1
001003 报销费 1

要求的sql 是 ：查询 没有子项（ht_kmno没有子项）的数据，现在这组数据，要的结果是：
kmno ht_kmname stano
ht_001001001001 公交费01 1
001001002 打的费 1
001002001 小单间 1
001002002001 大间01 1
001002003001 房间01 1
001002003004 房间02 1
001003 报销费 1

高手帮我看看，，，问题解决了，立马给分。。。

[dawugui]

select t.* from Forbug_CRMH t where not exists(select 1 from tb where ht_kmno like t.ht_kmno + '%')

[AcHerat]

select t.* 
from Forbug_CRMH t 
where not exists(select 1 from tb where ht_kmno like t.ht_kmno + '%')

[快溜]

select * from Forbug_CRMH a
where (select count(1) from Forbug_CRMH where charindex(a.ht_kmno,ht_kmno)>0)=1

[--小F--]

select
 * 
from
 Forbug_CRMH t 
where
 not exists(select 1 from tb where ht_kmno like t.ht_kmno + '%')

[AcHerat]

select t.* 
from Forbug_CRMH t 
where ht_kmno not in (select ht_kmno from tb where charindex(t.ht_kmno,ht_kmno) > 0 and ht_kmno <> t.ht_kmno)

[Rotel-刘志东]

select t.* from Forbug_CRMH t 
where not exists(select 1 from tb where ht_kmno like t.ht_kmno + '%')

[dawugui]

create table tb(ht_kmno varchar(20),ht_kmname varchar(10),stano int)
insert into tb values('001001'      , '交通费', 1)
insert into tb values('001001001'   , '公交费' ,1)
insert into tb values('001001001001', '公交费01', 1)
insert into tb values('001001002'   , '打的费', 1)
insert into tb values('001002'      , '住宿费', 1)
insert into tb values('001002001'   , '小单间', 1)
insert into tb values('001002002'   , '大间',1)
insert into tb values('001002002001', '大间01', 1)
insert into tb values('001002003'   , '房间',1)
insert into tb values('001002003001', '房间01', 1)
insert into tb values('001002003004', '房间02', 1)
insert into tb values('001003'      , '报销费', 1)
go

select t.* from tb t where not exists(select 1 from tb where t.ht_kmno <> ht_kmno and ht_kmno like t.ht_kmno + '%')

drop table tb

/*
ht_kmno              ht_kmname  stano       
-------------------- ---------- ----------- 
001001001001         公交费01      1
001001002            打的费        1
001002001            小单间        1
001002002001         大间01       1
001002003001         房间01       1
001002003004         房间02       1
001003               报销费        1

（所影响的行数为 7 行）
*/

[快溜]

create table tb(ht_kmno varchar(20),ht_kmname varchar(10),stano int)
insert into tb values('001001'      , '交通费', 1)
insert into tb values('001001001'   , '公交费' ,1)
insert into tb values('001001001001', '公交费01', 1)
insert into tb values('001001002'   , '打的费', 1)
insert into tb values('001002'      , '住宿费', 1)
insert into tb values('001002001'   , '小单间', 1)
insert into tb values('001002002'   , '大间',1)
insert into tb values('001002002001', '大间01', 1)
insert into tb values('001002003'   , '房间',1)
insert into tb values('001002003001', '房间01', 1)
insert into tb values('001002003004', '房间02', 1)
insert into tb values('001003'      , '报销费', 1)
go
select * from tb a
where (select count(1) from tb where charindex(a.ht_kmno,ht_kmno)>0)=1
/*
ht_kmno              ht_kmname  stano
-------------------- ---------- -----------
001001001001         公交费01      1
001001002            打的费        1
001002001            小单间        1
001002002001         大间01       1
001002003001         房间01       1
001002003004         房间02       1
001003               报销费        1

[fengyuananan]

谢谢大家的热心帮助。。。问题解决了。。。真的很感谢。。。
我用的是三楼的。。。没问题。。。
我每个都测过了。。。。能用不能用的 ，我都谢谢大家啊。。。。呵呵。这就结贴三分。。。。

[O爱咋咋地O]

select b.* from (select max(len(ht_kmno)) as length,substring(ht_kmno,1,6) as ht_kmno from dbo.Forbug_CRMH  group by substring(ht_kmno,1,6)) a join
dbo.Forbug_CRMH b on a.ht_kmno=substring(ht_kmno,1,6) and a.length=len(b.ht_kmno)

楼主试下

[webdesign_seo]

if object_id('tb') is not null 
	drop table tb
create table tb(ht_kmno varchar(20),ht_kmname varchar(10),stano int)
insert into tb values('001001'      , '交通费', 1)
insert into tb values('001001001'   , '公交费' ,1)
insert into tb values('001001001001', '公交费01', 1)
insert into tb values('001001002'   , '打的费', 1)
insert into tb values('001002'      , '住宿费', 1)
insert into tb values('001002001'   , '小单间', 1)
insert into tb values('001002002'   , '大间',1)
insert into tb values('001002002001', '大间01', 1)
insert into tb values('001002003'   , '房间',1)
insert into tb values('001002003001', '房间01', 1)
insert into tb values('001002003004', '房间02', 1)
insert into tb values('001003'      , '报销费', 1)
go
select * from tb
select * from tb a where not exists(select * from tb where charindex(a.ht_kmno,ht_kmno)>0 and len(a.ht_kmno)<len(ht_kmno))

[O爱咋咋地O]

我想错了，楼上发的代码错的

[webdesign_seo]

ht_kmno ht_kmname stano
-------------------- ---------- -----------
001001001001 公交费01 1
001001002 打的费 1
001002001 小单间 1
001002002001 大间01 1
001002003001 房间01 1
001002003004 房间02 1
001003 报销费 1

(7 行受影响)

[fengyuananan]

这个问题已经解决了，谢谢大家。。。