oracle 例如:一张表tb_city(id,name,code,parentid) 可形成树状结构。
数据如下:
0,中国,null
1, 北京,0
2,海淀,1
3,朝阳,1
4,东城,1
5,天津,0
6,和平,5
7,河东,5
8,河西,5
9,河北,5
10,红桥,5
11,南开,5
12,重庆,0
......
如何写SQL语句查询,北京,天津,重庆....子数据数量?SELECT COUNT(b.id) amount,f2.id FROM
(
SELECT id,parentid FROM tb_city
)b
right join
(
SELECT id,parentid FROM tb_city
)f2
on f2.id=b.parentid GROUP BY f2.id
SQL sqljoin树子数量
数据如下:
0,中国,null
1, 北京,0
2,海淀,1
3,朝阳,1
4,东城,1
5,天津,0
6,和平,5
7,河东,5
8,河西,5
9,河北,5
10,红桥,5
11,南开,5
12,重庆,0
......
如何写SQL语句查询,北京,天津,重庆....子数据数量?SELECT COUNT(b.id) amount,f2.id FROM
(
SELECT id,parentid FROM tb_city
)b
right join
(
SELECT id,parentid FROM tb_city
)f2
on f2.id=b.parentid GROUP BY f2.id
SQL sqljoin树子数量
select '0' as id,'中国' as name1,'' as pid from dual
union all
select '1' as id,'北京' as name1,'0' as pid from dual
union all
select '2' as id,'海淀' as name1,'1' as pid from dual
union all
select '3' as id,'朝陽' as name1,'1' as pid from dual
union all
select '4' as id,'東城' as name1,'1' as pid from dual
union all
select '5' as id,'天津' as name1,'0' as pid from dual
union all
select '6' as id,'和平' as name1,'5' as pid from dual
union all
select '7' as id,'河東' as name1,'5' as pid from dual
union all
select '8' as id,'河西' as name1,'5' as pid from dual
union all
select '9' as id,'河北' as name1,'5' as pid from dual
union all
select '10' as id,'紅橋' as name1,'5' as pid from dual
union all
select '11' as id,'南開' as name1,'5' as pid from dual
union all
select '12' as id,'重慶' as name1,'0' as pid from dual
)
select ltrim(SYS_CONNECT_BY_PATH(name1, '==>'), '==>')
from test
start with pid is null
connect by prior id = pid
SELECT id,parentid FROM tb_city WHERE parentid='0' 这条语句查出的结果集数量。