17,086
社区成员
![](https://csdnimg.cn/release/cmsfe/public/img/topic.427195d5.png)
![](https://csdnimg.cn/release/cmsfe/public/img/me.40a70ab0.png)
![](https://csdnimg.cn/release/cmsfe/public/img/task.87b52881.png)
![](https://csdnimg.cn/release/cmsfe/public/img/share-circle.3e0b7822.png)
with tmp as
(select 'a' col1, 'r1' as col2 from dual union all
select 'a' col1, 'r4' as col2 from dual union all
select 'a' col1, 'r1' as col2 from dual union all
select 'a' col1, 'r2' as col2 from dual union all
select 'b' col1, 'r1' as col2 from dual union all
select 'b' col1, 'r5' as col2 from dual union all
select 'c' col1, 'r3' as col2 from dual union all
select 'c' col1, 'r1' as col2 from dual union all
select 'c' col1, 'r3' as col2 from dual)
SELECT COL1, C_COL1, COL2, C_COL2
FROM (SELECT COL1, COL2, COUNT(1) AS C_COL2,
SUM(COUNT(1)) OVER(PARTITION BY COL1) AS C_COL1,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COUNT(1) DESC, COL2 ASC) AS RN
FROM TMP
GROUP BY COL1, COL2)
WHERE RN = 1
with tmp as
(select 'a' col1, 'r1' as col2 from dual union all
select 'a' col1, 'r4' as col2 from dual union all
select 'a' col1, 'r1' as col2 from dual union all
select 'a' col1, 'r2' as col2 from dual union all
select 'b' col1, 'r1' as col2 from dual union all
select 'b' col1, 'r5' as col2 from dual union all
select 'c' col1, 'r3' as col2 from dual union all
select 'c' col1, 'r1' as col2 from dual union all
select 'c' col1, 'r3' as col2 from dual)
SELECT *
FROM (SELECT COL1, C_COL1, COL2, C_COL2,
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY C_COL2 DESC, COL2 ASC) AS RN
FROM (SELECT COUNT(1) OVER(PARTITION BY COL1) AS C_COL1,
COUNT(1) OVER(PARTITION BY COL1, COL2) AS C_COL2, T.*
FROM TMP T))
WHERE RN = 1