显示的列好像是不定的?
select a.doc_id,a.cnt1
from
(select doc_id,
user_id,
count(*) cnt,
user_id||'('||count(*)||')' cnt1
from t_test
group by doc_id,user_id) adoc_id cnt1
a mm(2)
a nn(1)
b kk(1)
b mm(1)
b nn(1)目前是只能这个样子了,期待中
select a.doc_id,a.cnt1
from
(select doc_id,
user_id,
count(*) cnt,
user_id||'('||count(*)||')' cnt1
from t_test
group by doc_id,user_id) adoc_id cnt1
a mm(2)
a nn(1)
b kk(1)
b mm(1)
b nn(1)目前是只能这个样子了,期待中
---------- -------------------- --------------------
1 a mm
2 a nn
3 b mm
4 b nn
5 b kk
6 a mm
SQL> select doc_id||' '||substr(max(sys_connect_by_path(num, ',')), 2) doc_id from (
2 select doc_id, num, id, lead(id) over(partition by doc_id order by id ) idd from (
3 select doc_id, num, row_number() over(order by doc_id) id from (
4 (select doc_id, users||'('||count(*)||')' num from test1 group by doc_id, users))))
5 start with idd is null
6 connect by prior id=idd
7 group by doc_id;DOC_ID
----------------------------------------------------------------------------------------------------
a nn(1),mm(2)
b nn(1),mm(1),kk(1)