在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;
解决方案 »
- 一个疑难杂症,关于客户端远程连接mysql一定几率出现Can't connect to MySQL server on '192.168.0.9' (4)
- 分组取最小值
- mysql 怎么判断是一周之内的时间段?
- '乗' 这个字为何插入不到数据表里,何解?你遇到过吗?
- 索引错误
- 请wwwwb和ACMAIN_CHM来。
- 启动的时候显示Timeout error occurred trying to start MySQL Daemon.
- access的数据转到了mysql里,原来的asp程序基本都不能用?怎么解决?分全给了!
- 新手mysql的数据库连接问题
- MYSQL所在目录的问题
- 我在本地安装了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