现在有表
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
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
调试欢乐多
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 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.。。那样用吧
可能我表述得不好 ,我例子中是表的一部分,我数据很多就不能select 1,select 2.。。那样用吧
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那些是我列出来的例子,实际是我从数据库表中获取的数据来的