请大力，小黑，黄山，登山。。等高手看一下这个题，急！！

[yoki]

表t1
deptid deptcode
1 00101
2 00102
3 0010200001
4 001020000100001
5 00102000010000100001
6 0010200002
7 00103
8 0010300001
9 0010300002
10 001030000200001
要得到表t2
tem deptcode
1 00101 --后面没有子节点故tem为1
null 00102 --后面有子节点故tem为null
null 0010200001 --同上
null 001020000100001 --同上
1 00102000010000100001 --无，为1
1 0010200002 --无，为1
null 00103 --有，为null
1 0010300001 --无，为1
null 0010300002 --有，为null
1 001030000200001 --无，为null

[playyuer]

replace

[yoki]

to: playyuer(心上人)
什么意思？能详细点吗？？
用replace好像没用吧

[j9988]

update A set tem=(case when exists(select 1 from t2 where deptcode like A.deptcode+'_%') then null else 1 end) from t2 A

[playyuer]

select depcode,(select count(*) as a
from #t
where depcode like '%' + a.depcode + '%'
and a.depcode <> depcode
)
from #t a

[j9988]

select (case when exists(select 1 from t1 where deptcode like A.deptcode+'_%') then null else 1 end) as tem,deptcode from T1 A

[playyuer]

select depcode,(select count(*) as a
from #t
where depcode like a.depcode + '_%'
--and a.depcode <> depcode
)
from #t a

select depcode,(select count(*) as a
from #t
where len(replace(depcode,a.depcode,'')) <> len (depcode)
and a.depcode <> depcode
)
from #t a

[pengdali]

J老师可以的：

select (case when exists(select 1 from t1 where deptcode like A.deptcode+'_%') then null else 1 end) as tem,deptcode from T1 A

[pengdali]

都可以！哈哈。。。

[pengdali]

最简单：

select (select 1 from t1 where deptcode like A.deptcode+'_%') tem,deptcode from T1 A

[happydreamer]

select distinct a.deptid
from t a join t b on a.deptid<>b.deptid
where CHARINDEX(a.deptcode,b.deptcode)>=1

找到 2 3 4 7 9列需要改为null

[playyuer]

select (select max(1)
from #t
where depcode like a.depcode + '_%'
-- and a.depcode <> depcode
) as tem ,depcode
from #t a

[pengdali]

看错题目，哈哈。。。
正好反了哈哈，还是要case的，对不起！
select (select 1 from t1 where deptcode like A.deptcode+'_%') tem,deptcode from T1 A

[yoki]

谢谢大家~，分不多，对不住了

[happydreamer]

update #t
set deptid=null
where deptid in (select distinct a.deptid
from t a join t b on a.deptid<>b.deptid
where CHARINDEX(a.deptcode,b.deptcode)>=1 )

[happydreamer]

大力
你那个
select (select 1 from t1 where deptcode like A.deptcode+'_%') tem,deptcode from T1 A
有点问题 子查询返回的值多于一个



j老师 和playyuer(心上人) 老大的可以的