oracle count最大值,如何取COUNT后最大值

本帖最后由 bell6248 于 2017-3-21 14:44 编辑

很简单， 代码如下：

SQL> with t as

2  (

3    select 'A' PRE02, 'U' CHANNEL from dual

4    union all

5    select 'A' PRE02, 'U' CHANNEL from dual

6    union all

7    select 'A' PRE02, 'U' CHANNEL from dual

8    union all

9    select 'A' PRE02, 'J' CHANNEL from dual

10    union all

11    select 'B' PRE02, 'K' CHANNEL from dual

12    union all

13    select 'B' PRE02, 'K' CHANNEL from dual

14    union all

15    select 'B' PRE02, 'L' CHANNEL from dual

16    union all

17    select 'B' PRE02, 'P' CHANNEL from dual

18    union all

19    select 'C' PRE02, 'Y' CHANNEL from dual

20    union all

21    select 'C' PRE02, 'Y' CHANNEL from dual

22    union all

23    select 'C' PRE02, 'Y' CHANNEL from dual

24    union all

25    select 'C' PRE02, 'T' CHANNEL from dual

26  )

27  select  PRE02,

28          CHANNEL,

29          cnt

30  from

31  (select PRE02,

32          CHANNEL,

33          count(*) cnt,

34          dense_rank() over(partition by PRE02 order by count(*) desc) rn

35     from t

36    group by PRE02,CHANNEL)

37  where rn <= 1;

PRE02 CHANNEL        CNT

----- ------- ----------

A     U                3

B     K                2

C     Y                3

SQL>

如果你非要用max keep(), 方法如下， 但是有个问题， 如果同样的PRE02， CHANNEL最大数量的值超过一个， 那还是要用我前面给的方法

select  PRE02,

min(CHANNEL) keep(dense_rank last order by cnt) CHANNEL,

max(cnt) cnt

from

(select PRE02,

CHANNEL,

count(*) cnt

from t

group by PRE02,CHANNEL)

group by PRE02