在mysql下,谁能帮我把这个sql语句优化一下,谢谢了SELECT *
FROM (SELECT `country_name` , count( * ) AS num
FROM `lomo_track_201106`
GROUP BY `country_name`
ORDER BY num DESC
) AS a
LIMIT 0 , 20
UNION ALL SELECT 'other', sum( num ) AS num
FROM (SELECT `country_name` , count( * ) AS num
FROM `lomo_track_201106`
GROUP BY `country_name`
ORDER BY num DESC
LIMIT 20 , 1000000
) AS b;
FROM (SELECT `country_name` , count( * ) AS num
FROM `lomo_track_201106`
GROUP BY `country_name`
ORDER BY num DESC
) AS a
LIMIT 0 , 20
UNION ALL SELECT 'other', sum( num ) AS num
FROM (SELECT `country_name` , count( * ) AS num
FROM `lomo_track_201106`
GROUP BY `country_name`
ORDER BY num DESC
LIMIT 20 , 1000000
) AS b;
解决方案 »
- 关于drop table的一个疑问??????????
- 如何从sql server 2008迁移到MySQL!
- 关于修改mysql.user里root用户的密码的问题请教
- 问一个mysql 同时操作表的问题
- 表a 的 字段id是int where id='1'
- 请教一个相当白痴的问题
- 如何合并MYSQL 两个数据库的两张关键字不同的表?
- 超级菜鸟问题,在W2KS下安装完MYSQL以后,怎么启动啊,MYSQL有没有图形界面
- 奇怪!
- 最简单的SELECT语句出错?
- 我在本地安装了MySQL Server 5.1,如何连接到远程的一个MySQL服务器呢?
- mysqlslap的debug-info的信息都代表什么含义啊
将
SELECT `country_name` , count( * ) AS num
FROM `lomo_track_201106`
GROUP BY `country_name`
ORDER BY num DESC
存为 VIEW
不想用视图 也不想用存储过程
要求就是 查询出来数量在前二十的国家名称 然后将其他的国家的数量归到other字段里面
可以考虑使用减法来统计other的count,理论上在country数量较多的时候,会有点优势 SELECT country_name, num FROM
(SELECT country_name, count(*) AS num FROM lomo_track_201106 GROUP BY country_name ORDER BY num DESC LIMIT 20) a
UNION ALL
SELECT 'other', b.total - sum(a.num) FROM
(SELECT country_name, count(*) AS num FROM lomo_track_201106 GROUP BY country_name ORDER BY num DESC LIMIT 20) a,
(SELECT count(*) AS total FROM lomo_track_201106) b因为无条件统计一张表的记录数,这个不需要扫描表或索引的。
(SELECT country_name, count(*) AS num FROM lomo_track_201106 GROUP BY country_name ORDER BY num DESC LIMIT 20)
加
(SELECT country_name, count(*) AS num FROM lomo_track_201106 GROUP BY country_name ORDER BY num DESC LIMIT 20,1000000)的吧?
SELECT 'other', b.total - sum(a.num) FROM