表A
id res all
1 a/b/c true
2 a/c/b true
3 c/c/b true
4 d/c/b true
现在需求是这样 计算出res中第一个/之前的分组
结果 :
count resFirst
2 a
1 c
1 d
谢谢。
id res all
1 a/b/c true
2 a/c/b true
3 c/c/b true
4 d/c/b true
现在需求是这样 计算出res中第一个/之前的分组
结果 :
count resFirst
2 a
1 c
1 d
谢谢。
with tmp as
(
select 1 id, 'a/b/c' res, 'true' al from dual
union all
select 2 id, 'a/c/b' res, 'true' al from dual
union all
select 3 id, 'c/c/b' res, 'true' al from dual
union all
select 4 id, 'd/c/b' res, 'true' al from dual
)
select count(1),substr(res, 1, instr(res, '/') - 1) resfirst from tmp group by substr(res, 1, instr(res, '/') - 1)
order by 1 desc
--substr加instr
SQL> edi
已写入 file afiedt.buf 1 with tb as
2 (
3 select 1 id, 'a/b/c' res, 'true' al from dual
4 union all
5 select 2 id, 'a/c/b' res, 'true' al from dual
6 union all
7 select 3 id, 'c/c/b' res, 'true' al from dual
8 union all
9 select 4 id, 'd/c/b' res, 'true' al from dual
10 )
11 select substr(res,1,instr(res,'/',1)),count(*) from tb
12 group by substr(res,1,instr(res,'/',1))
13* order by substr(res,1,instr(res,'/',1))
SQL> /SUBSTR(RES COUNT(*)
---------- ----------
a/ 2
c/ 1
d/ 1
--改下
SQL> edi
已写入 file afiedt.buf 1 with tb as
2 (
3 select 1 id, 'a/b/c' res, 'true' al from dual
4 union all
5 select 2 id, 'a/c/b' res, 'true' al from dual
6 union all
7 select 3 id, 'c/c/b' res, 'true' al from dual
8 union all
9 select 4 id, 'd/c/b' res, 'true' al from dual
10 )
11 select substr(res,1,instr(res,'/',1)-1),count(*) from tb
12 group by substr(res,1,instr(res,'/',1)-1)
13* order by substr(res,1,instr(res,'/',1)-1)
SQL> /SUBSTR(RES COUNT(*)
---------- ----------
a 2
c 1
d 1
(
select 1 id, 'a/b/c' res, 'true' al from dual
union all
select 2 id, 'a/c/b' res, 'true' al from dual
union all
select 3 id, 'c/c/b' res, 'true' al from dual
union all
select 4 id, 'd/c/b' res, 'true' al from dual
)
select count(1),regexp_substr(res, '^[^/]') resfirst from tmp group by regexp_substr(res, '^[^/]')
order by 1 desc COUNT(1) RESFIRST
---------- --------
2 a
1 c
1 d