比如表
name count
A 10
B 5
C 20
D 60
E 80
F 2
G 7
H 3共8条记录name保证唯一现在要查询count的TOP5,以及剩下3条的总和:E 80
D 60
C 20
A 10
G 7
其它 10希望不要用create temporary tabe
name count
A 10
B 5
C 20
D 60
E 80
F 2
G 7
H 3共8条记录name保证唯一现在要查询count的TOP5,以及剩下3条的总和:E 80
D 60
C 20
A 10
G 7
其它 10希望不要用create temporary tabe
解决方案 »
- 存储过程 SQL Security指定DEFINER但是其它的用户还是可以调用这个存储过程?
- 求一个MySQL从入门到精通的学习文档
- 关于Mysql Administrator
- MYSQL怎么实现记录锁?请高手帮忙,高分
- 在window 2000 server 上安装mysql后,正常启动时初始化用户为:test密码为:test为什么在次登录的时候就不用密码也能登录为什么?60
- 关于mysql数据给定范围查询的问题
- mysql 表行数据根据某个相同字段合并的的sql语句怎么写
- 在mysql的workbench里怎么直接改数据
- mysql怎么限制最大查询的数量
- 哪位大神有Navicat for mysql 11.0.10注册码
- 求解,数据库如何存入某某数的平方这种?
- #2013 - reading initial communication packet,system error: 111
Query OK, 0 rows affected (0.13 sec)mysql> insert into temp values ('A',10),('B',5),('C',20),('D',60),('E',80),('F',
2),('G',7),('H',3);
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0mysql> select * from temp;
+------+------+
| name | co |
+------+------+
| A | 10 |
| B | 5 |
| C | 20 |
| D | 60 |
| E | 80 |
| F | 2 |
| G | 7 |
| H | 3 |
+------+------+
8 rows in set (0.00 sec)mysql> select * from (select * from temp order by co desc limit 0,5 ) a union
select '其它' as name , sum(a.co) as co from (select * from temp order by co d
esc limit 5,3 ) a;
+------+------+
| name | co |
+------+------+
| E | 80 |
| D | 60 |
| C | 20 |
| A | 10 |
| G | 7 |
| 其它 | 10 |
+------+------+
6 rows in set (0.00 sec)
from tb
order by count desc limit 5
union all
select '其他',sum(count)
from tb
where name not in (select name
from tb
order by count desc limit 5)
union all
select '其它',sum(`count`) from 比如表 where name not in (select name from 比如表 order by `count` desc limit 5)
UNION ALL
SELECT '其它',SUM(co) FROM temp a WHERE 5<=(SELECT COUNT(*) FROM temp WHERE a.co<co )