计数不包含二级单位自身,如果要包含自身,将>改成>=
select left(单位,6) as 二级单位,count(*) as 下级单位总数
from tb where len(单位)>6 group by left(单位,6)
select left(单位,6) as 二级单位,count(*) as 下级单位总数
from tb where len(单位)>6 group by left(单位,6)
调试欢乐多
INSERT @a SELECT '001'
UNION ALL SELECT '001001'
UNION ALL SELECT '001001001'
UNION ALL SELECT '001001002'
UNION ALL SELECT '001001003'
UNION ALL SELECT '001002'
UNION ALL SELECT '001002001'
UNION ALL SELECT '001002002'
UNION ALL SELECT '001002003'
UNION ALL SELECT '001003'
SELECT
LEFT(a,6) xx,count(1)-1 yy
FROM @a
GROUP BY LEFT(a,6)
HAVING min(len(LEFT(a,6)))=6--result
/*xx yy
------------ -----------
001001 3
001002 3
001003 0(所影响的行数为 3 行)*/
from 表 as a
where len( 单位 ) = 6
INSERT @a SELECT '001'
UNION ALL SELECT '001001'
UNION ALL SELECT '001001001'
UNION ALL SELECT '001001002'
UNION ALL SELECT '001001003'
UNION ALL SELECT '001002'
UNION ALL SELECT '001002001'
UNION ALL SELECT '001002002'
UNION ALL SELECT '001002003'
UNION ALL SELECT '001003'
select left(a,6),count(1)-1 from @a
where len(a)>=6
group by left(a,6)