SELECT 部门,COUNT(*) FROM tt GROUP BY 部门 ORDER BY COUNT(*) DESC
--测试数据 create table tt( t_name varchar2(100)); insert into tt select '张三(开发一部)' from dual union all select '李四(开发一部)' from dual union all select '王五(开发一部)' from dual union all select '赵六(开发二部)' from dual union all select '关老爷(市场部)' from dual; --执行查询 select substr(t_name,instr(t_name,'(',1),instr(t_name,')',1)),count(*) from tt group by substr(t_name,instr(t_name,'(',1),instr(t_name,')',1)) order by decode(substr(t_name,instr(t_name,'(',1),instr(t_name,')',1)),'(开发一部)',0,'(开发二部)',1,'(市场部)',2) --输出结果 (开发一部)3 (开发二部)1 (市场部) 1
select count(*) as Num, deptno from t_name group by deptno;
SELECT 部门,COUNT(*) AS Num FROM tt GROUP BY 部门 ORDER BY Num DESC
select * from ( select substr(t_name,instr(t_name,'(')),count(1) countN from tt group by substr(t_name,instr(t_name,'(')) ) a order by countN
create table tt( t_name varchar2(100));
insert into tt
select '张三(开发一部)' from dual union all
select '李四(开发一部)' from dual union all
select '王五(开发一部)' from dual union all
select '赵六(开发二部)' from dual union all
select '关老爷(市场部)' from dual;
--执行查询
select substr(t_name,instr(t_name,'(',1),instr(t_name,')',1)),count(*) from tt
group by substr(t_name,instr(t_name,'(',1),instr(t_name,')',1))
order by decode(substr(t_name,instr(t_name,'(',1),instr(t_name,')',1)),'(开发一部)',0,'(开发二部)',1,'(市场部)',2)
--输出结果
(开发一部)3
(开发二部)1
(市场部) 1
(
select substr(t_name,instr(t_name,'(')),count(1) countN
from tt
group by substr(t_name,instr(t_name,'('))
) a
order by countN