--上面的写错了一些,改一下:select a.dir,[count]=count(b.class) from 表a a join 表b b on b.dir like a.dir+'%' group by a.dir
--下面是数据测试--测试数据 declare @表a table(dir varchar(10)) insert into @表a select '1' union all select '1/01' union all select '1/01/1' union all select '1/02' union all select '2'declare @表b table(dir varchar(10),class varchar(10)) insert into @表b select '1/01','b' union all select '1/01','bb' union all select '1/01/1','c' union all select '1/01/1','cc' union all select '1/01/1','cd' union all select '1/02','e' union all select '2','f'--统计 select a.dir,[count]=count(b.class) from @表a a join @表b b on b.dir like a.dir+'%' group by a.dir/*--测试结果 dir count ---------- ----------- 1 6 1/01 5 1/01/1 3 1/02 1 2 1(所影响的行数为 5 行) --*/
from 表a a join 表b b on b.dir like a.dir+'%'
group by a.dir
declare @表a table(dir varchar(10))
insert into @表a
select '1'
union all select '1/01'
union all select '1/01/1'
union all select '1/02'
union all select '2'declare @表b table(dir varchar(10),class varchar(10))
insert into @表b
select '1/01','b'
union all select '1/01','bb'
union all select '1/01/1','c'
union all select '1/01/1','cc'
union all select '1/01/1','cd'
union all select '1/02','e'
union all select '2','f'--统计
select a.dir,[count]=count(b.class)
from @表a a join @表b b on b.dir like a.dir+'%'
group by a.dir/*--测试结果
dir count
---------- -----------
1 6
1/01 5
1/01/1 3
1/02 1
2 1(所影响的行数为 5 行)
--*/