问题就是 按一字段分组后再选出另一字段出现频率最高的
我获取了 一个表 logs
module dept
aa 01
aa 01
bb 02
bb 01
aa 03
bb 02
我需要获取各个dept用得最多的module
------解决思路----------------------
with TT as (select moudel, dept, count(*) cc from logs group by moudel, dept)
select moudel, dept from TT where cc=(select max(cc) from TT) order by moudel;
------解决思路----------------------
-- 刚才写成 sqlserver 的语法了。今天不在状态。。。
SQL> create table logs(module varchar(10),dept varchar(10));
Table created
SQL> begin
2 insert into logs values( 'aa', '01');
3 insert into logs values( 'aa', '01');
4 insert into logs values( 'bb', '02');
5 insert into logs values( 'bb', '01');
6 insert into logs values( 'aa', '03');
7 insert into logs values( 'bb', '02');
8 commit ;
9 end;
10 /
PL/SQL procedure successfully completed
SQL> select * from logs ;
MODULE DEPT
---------- ----------
aa 01
aa 01
bb 02
bb 01
aa 03
bb 02
6 rows selected
SQL> /
MODULE DEPT
---------- ----------
aa 01
aa 01
bb 02
bb 01
aa 03
bb 02
6 rows selected
SQL> with x as(
2 select module, dept , COUNT(*) c from logs group by module , dept
3 ),
4 xx as(
5 select x.* , RANK() over (order by x.c desc) rn from x
6 )
7 select module,dept from xx where rn = 1 ;
MODULE DEPT
---------- ----------
aa 01
bb 02
SQL> /
MODULE DEPT
---------- ----------
aa 01
bb 02
SQL> drop table logs purge ;
Table dropped
SQL>
------解决思路----------------------
引用版主，少了一个partition by dept 语句应该是下面这个。
with x as(
select module, dept , COUNT(*) c from logs group by module , dept
),
xx as(
select x.* , RANK() over (partition by dept order by x.c desc) rn from x
)
select module,dept from xx where rn = 1 ;
------解决思路----------------------
SELECT t1.dept,
t2.mod
FROM
(SELECT dept,
MAX(NUM) NUM
FROM
(SELECT dept,
MOD,
COUNT(*) NUM
FROM logs
GROUP BY dept,MOD)t
GROUP BY dept)t1
LEFT JOIN
(SELECT dept,
MOD,
COUNT(*) NUM
FROM logs
GROUP BY dept,MOD)t2
ON t1.dept=t2.dept
AND t1.num=t2.num
ORDER BY t1.dept DESC