create table tb(dm varchar(10),mc varchar(10),childnum int,type varchar(10))
insert into tb select '01','名称',null,'a'
insert into tb select '0101','mi',null,'a'
insert into tb select '0102',null,null,'a'
insert into tb select '010101','kk',null,'a'
go
update t set childnum=(select COUNT(*) from tb where LEFT(dm,LEN(t.dm))=t.dm and LEN(dm)=LEN(t.dm)+2) from tb t
select * from tb
/*
dm         mc         childnum    type
---------- ---------- ----------- ----------
01         名称         2           a
0101       mi         1           a
0102       NULL       0           a
010101     kk         0           a

(4 行受影响)

*/
go
drop table tb

------解决方案--------------------
SQL code
use Tempdb
go
--> --> 
 
declare @T table([dm] nvarchar(10),[mc] nvarchar(10),[childnum] INT,[type] nvarchar(10))
Insert @T([dm],[mc],[type])
select N'01',N'名称',N'a' union all
select N'0101',N'mi',N'a' union all
select N'0102',N'',N'a' union all
select N'010101',N'kk',N'a'
 
UPDATE t
SET [childnum]=(SELECT COUNT(1) FROM @T WHERE [type]=t.[type] AND [dm] LIKE t.[dm]+'__')
from @T AS t
SELECT * FROM @T

/*
dm    mc    childnum    type
01    名称    2    a
0101    mi    1    a
0102        0    a
010101    kk    0    a
*/

------解决方案--------------------
SQL code

declare @t table (dm varchar(6),mc varchar(4),childnum int,type varchar(1))
insert into @t
select '01','名称',NULL,'a' union all
select '0101','mi',NULL,'a' union all
select '0102',null,NULL,'a' union all
select '010101','kk',NULL,'a'

UPDATE  @t
SET     childnum = ( SELECT COUNT(1)
                     FROM   @t
                     WHERE  a.dm = LEFT(dm, LEN(dm) - 2)
                   )
FROM    @t a
 
SELECT * FROM @T
/*
dm     mc   childnum    type
------ ---- ----------- ----
01     名称   2           a
0101   mi   1           a
0102   NULL 0           a
010101 kk   0           a
*/