sql一语句写法

求助 sql一语句写法
有表a 字段有 
dm mc childnum type
01 名称 a
0101 mi a
0102 a
010101 kk a
以下多行


这个怎么用sql语句把childnum 赋值 比如01的childnum=2 0101的是1 0102的是0

用一个语句把这张表每一行childnum都赋值

[解决办法]

SQL code

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'goupdate t set childnum=(select COUNT(*) from tb where LEFT(dm,LEN(t.dm))=t.dm and LEN(dm)=LEN(t.dm)+2) from tb tselect * from tb/*dm         mc         childnum    type---------- ---------- ----------- ----------01         名称         2           a0101       mi         1           a0102       NULL       0           a010101     kk         0           a(4 行受影响)*/godrop table tb
[解决办法]
SQL code
use Tempdbgo--> -->  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 allselect N'0101',N'mi',N'a' union allselect N'0102',N'',N'a' union allselect N'010101',N'kk',N'a' UPDATE tSET [childnum]=(SELECT COUNT(1) FROM @T WHERE [type]=t.[type] AND [dm] LIKE t.[dm]+'__')from @T AS tSELECT * FROM @T/*dm    mc    childnum    type01    名称    2    a0101    mi    1    a0102        0    a010101    kk    0    a*/
[解决办法]
SQL code
declare @t table (dm varchar(6),mc varchar(4),childnum int,type varchar(1))insert into @tselect '01','名称',NULL,'a' union allselect '0101','mi',NULL,'a' union allselect '0102',null,NULL,'a' union allselect '010101','kk',NULL,'a'UPDATE  @tSET     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           a0101   mi   1           a0102   NULL 0           a010101 kk   0           a*/