请问各位我有这样一张表 Tc1 c2 c3
A B 20
A B 10
A C 10
A D 40
A E 35
A F 5
我有这样一种需求,按c1,c2两列 来统计数 排前三位 但还要列出c1,c2 所有重复的列,如下结果
c1 c2 c3
A D 40
A E 35
A B 20
A B 10这样的SQL 该怎么写呢 谢谢
A B 20
A B 10
A C 10
A D 40
A E 35
A F 5
我有这样一种需求,按c1,c2两列 来统计数 排前三位 但还要列出c1,c2 所有重复的列,如下结果
c1 c2 c3
A D 40
A E 35
A B 20
A B 10这样的SQL 该怎么写呢 谢谢
FROM (SELECT D.C1, D.C2, D.C3, RANK() OVER(ORDER BY SU) RU
FROM (SELECT T.C1,
T.C2,
T.C3,
SUM(C3) OVER(PARTITION BY T.C1, T.C2) SU
FROM T T) D) R
WHERE R.RU <= 3;
from(
select c1,c2,c3,rank() over(order by c3 desc) rn from test_06
) where rn<=3
union
select * from test_06
where (c1,c2) in(
select c1,c2 from test_06
group by c1,c2
having count(1)>1)
/
FROM (SELECT D.C1, D.C2, D.C3, RANK() OVER(ORDER BY SU DESC) RU
FROM (SELECT T.C1,
T.C2,
T.C3,
SUM(C3) OVER(PARTITION BY T.C1, T.C2) SU
FROM T T) D) R
WHERE R.RU <= 3;
A B 20
A B 10
A C 10
A D 40
A E 35
A F 15
我有这样一种需求,按c1,c2两列 来统计数 排前三位 但还要列出c1,c2 所有重复的列,如下结果
c1 c2 c3
A D 40
A E 35
A B 20
A B 10 这样的SQL 该怎么写呢 谢谢
或许这样描述更好
2 A E 35
3 A B 20
4 A B 10
SELECT R.C1, R.C2, R.C3
FROM (SELECT D.C1, D.C2, D.C3, RANK() OVER(ORDER BY SU DESC) RU
FROM (SELECT T.C1,
T.C2,
T.C3,
SUM(C3) OVER(PARTITION BY T.C1, T.C2) SU
FROM T T) D) R
WHERE R.RU <= 3;
RANK() 是分析函数~ 它的意思是排名次! R.RU <= 3的意思就是前三名,像 1,2,3,3 有2个前3名!
给你发的你都不在你机器上跑跑么?
select * from (select * from t order by c3 desc) where rownum<=3
union
select * from t a where EXISTS
(select c1,c2,count(1) from t b where a.c1=b.c1 and a.c2=b.c2 group by c1,c2having count(1)>1)
)
order by c3 desc这个可以得到你想要的结果
c1 c2 c3
A D 40
A E 35
A B 20
A B 10
你这个好像按c1,c2有问题样的。。应该是
c1 c2 c3
A D 40
A E 35
A B 30
A B 30
SELECT R.C1, R.C2, R.SU
FROM (SELECT D.C1, D.C2, D.C3, D.SU, RANK() OVER(ORDER BY SU DESC) RU
FROM (SELECT T.C1,
T.C2,
T.C3,
SUM(C3) OVER(PARTITION BY T.C1, T.C2) SU
FROM T T) D) R
WHERE R.RU <= 3;