oralce语句按出现次数排序问题

[yanbinquan]

现在有表
id name count
1 a 1
2 b 5
3 c 4
4 a 3
5 b 2

我想实现把count大于1的数据按name的出现次数的倒序排序

想得到的结果
id name count
2 b 5
5 b 2
3 c 4
4 a 3

[NIan_jun]

with t as (
select 1 a,'a' b,1 c from dual union all
select 2 a,'b',5 from dual union all
select 3 a,'c',4 from dual union all
select 4 a,'a',3 from dual union all
select 5 a,'b',2 from dual 
) 
select a,b,c from (
select a,b,c,count(*)over(partition by b ) d from t where c>1
) order by d desc,c desc

[善若止水]

with t as (
select 1 ID,'a' NAME,1 COUNT from dual union all
select 2 ,'b',5 from dual union all
select 3 ,'c',4 from dual union all
select 4 ,'a',3 from dual union all
select 5 ,'b',2 from dual 
) 
SELECT  ID,NAME,COUNT FROM t 
WHERE t.count>1
ORDER BY NAME DESC,COUNT DESC

[yanbinquan]

引用 1 楼 NIan_jun 的回复:

with t as (
select 1 a,'a' b,1 c from dual union all
select 2 a,'b',5 from dual union all
select 3 a,'c',4 from dual union all
select 4 a,'a',3 from dual union all
select 5 a,'b',2 from dual 
) 
select a,b,c from (
select a,b,c,count(*)over(partition by b ) d from t where c>1
) order by d desc,c desc

可能我表述得不好 ，我例子中是表的一部分，我数据很多就不能select 1，select 2.。。那样用吧

[yanbinquan]

引用 2 楼 zhaoxiangchong 的回复:

with t as (
select 1 ID,'a' NAME,1 COUNT from dual union all
select 2 ,'b',5 from dual union all
select 3 ,'c',4 from dual union all
select 4 ,'a',3 from dual union all
select 5 ,'b',2 from dual 
) 
SELECT  ID,NAME,COUNT FROM t 
WHERE t.count>1
ORDER BY NAME DESC,COUNT DESC

可能我表述得不好 ，我例子中是表的一部分，我数据很多就不能select 1，select 2.。。那样用吧

[yanbinquan]

还没能解决 有朋友帮忙一下吗

[persist-win]

2楼和3楼的已经告知你答案了，将2楼和3楼的结合一下，如下： SQL> col name for a4 SQL> with t as ( 2 select 1 ID,'a' NAME,1 COUNT from dual union all 3 select 2 ,'b',5 from dual union all 4 select 3 ,'c',4 from dual union all 5 select 4 ,'a',3 from dual union all 6 select 5 ,'b',2 from dual 7 ) 8 SELECT ID,NAME,COUNT FROM ( 9 select ID,NAME,COUNT,count(*)over(partition by name ) d from t where count>1 10 ) order by d desc,count desc; ID NAME COUNT ---------- ---- ---------- 2 b 5 5 b 2 3 c 4 4 a 3 能实现你上面想要的形式； 如你所说表中还有其他数据，没关系。 sql中 with t as ( select 1 ID,'a' NAME,1 COUNT from dual union all select 2 ,'b',5 from dual union all select 3 ,'c',4 from dual union all select 4 ,'a',3 from dual union all select 5 ,'b',2 from dual ) 部分是构建一个临时虚拟的表t,为的是实现你要的那种形式。 关键的是后面的sql： SELECT ID,NAME,COUNT FROM ( select ID,NAME,COUNT,count(*)over(partition by name ) d from t where count>1 ) order by d desc,count desc 这段sql才是实现功能的。你只需将其中的表t换成你的表名即可。

[yanbinquan]

引用 6 楼 langkeziju 的回复:

2楼和3楼的已经告知你答案了，将2楼和3楼的结合一下，如下： SQL> col name for a4 SQL> with t as ( 2 select 1 ID,'a' NAME,1 COUNT from dual union all 3 select 2 ,'b',5 from dual union all 4 select 3 ,'c',4 from dual union all 5 select 4 ,'a',3 from dual union all 6 select 5 ,'b',2 from dual 7 ) 8 SELECT ID,NAME,COUNT FROM ( 9 select ID,NAME,COUNT,count(*)over(partition by name ) d from t where count>1 10 ) order by d desc,count desc; ID NAME COUNT ---------- ---- ---------- 2 b 5 5 b 2 3 c 4 4 a 3 能实现你上面想要的形式； 如你所说表中还有其他数据，没关系。 sql中 with t as ( select 1 ID,'a' NAME,1 COUNT from dual union all select 2 ,'b',5 from dual union all select 3 ,'c',4 from dual union all select 4 ,'a',3 from dual union all select 5 ,'b',2 from dual ) 部分是构建一个临时虚拟的表t,为的是实现你要的那种形式。 关键的是后面的sql： SELECT ID,NAME,COUNT FROM ( select ID,NAME,COUNT,count(*)over(partition by name ) d from t where count>1 ) order by d desc,count desc 这段sql才是实现功能的。你只需将其中的表t换成你的表名即可。

a，b，c，d那些是我列出来的例子，实际是我从数据库表中获取的数据来的

[shy315]

6L的意思是，把1L和2L的sql中“with t as (...)”这部分去掉，剩下那部分替换你真实的表名和字段名，就是你要的结果了。