SQL> create table t_get_cnt 2 (dept varchar2(100), 3 time varchar2(4), 4 name varchar2(100));
Table created
SQL> select * from t_get_cnt;
DEPT TIME NAME -------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------- 某某部门 2007 张三 某某部门 2007 李四 某某部门 2007 王五 某某部门 2008 周正
SQL> SQL> SELECT dept, MAX(cnt) 2 FROM (SELECT dept, 3 wm_concat(TIME || '(' || COUNT(*) || ')') over(PARTITION BY dept ORDER BY TIME) cnt 4 FROM t_get_cnt 5 GROUP BY dept, TIME) 6 GROUP BY dept;
with tab as ( select '01' dep, 2007 yearC, 'zhang3' name from dual union all select '01',2007,'li4' from dual union all select '01',2008,'ma5' from dual union all select '02',2007,'wang6' from dual ) select dep, wm_concat(condition) from (select dep, to_char(yearC) || '(' || to_char(count(name)) || ')' condition from tab group by dep,yearC order by dep) group by dep ------------------------------------------ 01 2007(2),2008(1) 02 2007(1)
2 (dept varchar2(100),
3 time varchar2(4),
4 name varchar2(100));
Table created
SQL> select * from t_get_cnt;
DEPT TIME NAME
-------------------------------------------------------------------------------- ---- --------------------------------------------------------------------------------
某某部门 2007 张三
某某部门 2007 李四
某某部门 2007 王五
某某部门 2008 周正
SQL>
SQL> SELECT dept, MAX(cnt)
2 FROM (SELECT dept,
3 wm_concat(TIME || '(' || COUNT(*) || ')') over(PARTITION BY dept ORDER BY TIME) cnt
4 FROM t_get_cnt
5 GROUP BY dept, TIME)
6 GROUP BY dept;
DEPT MAX(CNT)
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
某某部门 2007(3),2008(1)
SQL>
with tab as
(
select '01' dep, 2007 yearC, 'zhang3' name from dual union all
select '01',2007,'li4' from dual union all
select '01',2008,'ma5' from dual union all
select '02',2007,'wang6' from dual
)
select dep, wm_concat(condition) from (select dep, to_char(yearC) || '(' || to_char(count(name)) || ')' condition from tab group by dep,yearC order by dep) group by dep
------------------------------------------
01 2007(2),2008(1)
02 2007(1)