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高手帮我看看,,,问题解决了,立马给分
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高手帮我看看,,,问题解决了,立马给分
select t.*
from Forbug_CRMH t
where not exists(select 1 from tb where ht_kmno like t.ht_kmno + '%')
where (select count(1) from Forbug_CRMH where charindex(a.ht_kmno,ht_kmno)>0)=1
*
from
Forbug_CRMH t
where
not exists(select 1 from tb where ht_kmno like t.ht_kmno + '%')
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)
where not exists(select 1 from tb where ht_kmno like t.ht_kmno + '%')
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)
goselect 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 行)
*/
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
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)楼主试下
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))
ht_kmno ht_kmname stano
-------------------- ---------- -----------
001001001001 公交费01 1
001001002 打的费 1
001002001 小单间 1
001002002001 大间01 1
001002003001 房间01 1
001002003004 房间02 1
001003 报销费 1(7 行受影响)