SELECT SUM(col)/(SELECT SUM(col) FROM tb ORDER BY col DESC LIMIT 10) FROM tb;
一个字段的总和减去这个字段前十行最大数值的总和? 很难理解,建议举例说明一个字段的总和 (这个容易 sum(fld)) 减去 ( - ) 前十行最大数值 (前十行的最大数值?按什么排序?) 前十行最大数值的总和 (? 最大数值不就一个,一个数的总和有什么意义?)如果是 最大前十行数值的总和 则按一楼的就可以了。select sum(fld)-(select sum(fld) from (select fld from table1 order by fld desc limit 10) t ) from table1;
借宝地问一下,mysql子查询不是不支持limit的吗?这里不是理解为子查询吗?
支持啊。至少5.1可以了。mysql> select version(); +----------------------+ | version() | +----------------------+ | 5.1.33-community-log | +----------------------+ 1 row in set (0.00 sec)mysql> select id from t5; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 11 | +------+ 5 rows in set (0.00 sec)mysql> mysql> select sum(id), -> (select sum(id) from (select id from t5 order by id desc limit 2) t) -> from t5; +---------+----------------------------------------------------------------------+ | sum(id) | (select sum(id) from (select id from t5 order by id desc limit 2) t) | +---------+----------------------------------------------------------------------+ | 21 | 15 | +---------+----------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql>
在确是 "最大前十行数值的总和 ".但是好像不行.查出来的值是 1 SELECT SUM(`SUM`)/(SELECT SUM(`SUM`) FROM isicountrypubbyyear ORDER BY `SUM` DESC LIMIT 10) FROM isicountrypubbyyear 是不是我的版本有问题? 版本对的呀 5.1.30-community-log以上查询是在PHPMYADMIN中实行的.我只执行 SELECT SUM(`SUM`) FROM isicountrypubbyyear ORDER BY `SUM` DESC LIMIT 10; 它把整个`SUM`的字段全部求和.而不是最大的前十行数值.迷失中......
select sum(`SUM`)-(select sum(`SUM`) from (select `SUM` from isicountrypubbyyear order by `SUM` desc limit 10) t) from `isicountrypubbyyear`
的确是你说的这样,因此取最大10个的总和他有这样处理: select sum(fld) from ((select fld from {$table} limit 10) a)这里临时表a中只包含最大的10条,且临时表a中只有一个字段fld,再在a上加sum操作
SUM(col)/(SELECT SUM(col) FROM tb ORDER BY col DESC LIMIT 10)
FROM tb;
减去 ( - )
前十行最大数值 (前十行的最大数值?按什么排序?)
前十行最大数值的总和 (? 最大数值不就一个,一个数的总和有什么意义?)如果是 最大前十行数值的总和
则按一楼的就可以了。select sum(fld)-(select sum(fld) from (select fld from table1 order by fld desc limit 10) t ) from table1;
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql> select id from t5;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 11 |
+------+
5 rows in set (0.00 sec)mysql>
mysql> select sum(id),
-> (select sum(id) from (select id from t5 order by id desc limit 2) t)
-> from t5;
+---------+----------------------------------------------------------------------+
| sum(id) | (select sum(id) from (select id from t5 order by id desc limit 2) t) |
+---------+----------------------------------------------------------------------+
| 21 | 15 |
+---------+----------------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
它把整个`SUM`的字段全部求和.而不是最大的前十行数值.迷失中......
sum(`SUM`)-(select sum(`SUM`) from (select `SUM` from isicountrypubbyyear order by `SUM` desc limit 10) t)
from
`isicountrypubbyyear`
的确是你说的这样,因此取最大10个的总和他有这样处理: select sum(fld) from ((select fld from {$table} limit 10) a)这里临时表a中只包含最大的10条,且临时表a中只有一个字段fld,再在a上加sum操作