这样的如何统计

[hbgzg3006]

表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
谢谢。

[ngx20080110]

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

[ngx20080110]

10g 和 11g可以用正则表达式

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),regexp_substr(res, '^[^/]') resfirst from tmp group by regexp_substr(res, '^[^/]')
order by 1 desc

COUNT(1) RESFIRST
---------- --------
2 a
1 c
1 d

[心中的彩虹]

[code=SQL]
--改下
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

[hbgzg3006]

oracle就是强大！谢谢。