MySQL查询获取最大累积值
为此，将聚合函数COUNT（*）与子查询一起使用。还使用GROUPBY。
让我们创建一个表-
mysql> create table demo23 −> ( −> id int not null auto_increment primary key, −> value1 int, −> value2 int −> );
借助insert命令将一些记录插入表中-
mysql> insert into demo23(value1,value2) values(5,600); mysql> insert into demo23(value1,value2) values(20,800); mysql> insert into demo23(value1,value2) values(7,400); mysql> insert into demo23(value1,value2) values(6,500); mysql> insert into demo23(value1,value2) values(10,300); mysql> insert into demo23(value1,value2) values(11,500);
使用select语句显示表中的记录-
mysql> select *from demo23;
这将产生以下输出-
+----+--------+--------+ | id | value1 | value2 | +----+--------+--------+ | 1 | 5 | 600 | | 2 | 20 | 800 | | 3 | 7 | 400 | | 4 | 6 | 500 | | 5 | 10 | 300 | | 6 | 11 | 500 | +----+--------+--------+ 6 rows in set (0.00 sec)
以下是最大累积量的查询-
mysql> select total_value, count(*) as number_of_occurrences −> from ( −> select value1*value2 as total_value −> from demo23 −> ) t −> group by total_value −> order by total_value desc −> limit 1 ;
这将产生以下输出-
+-------------+-----------------------+ | total_value | number_of_occurrences | +-------------+-----------------------+ | 16000 | 1 | +-------------+-----------------------+ 1 row in set (0.00 sec)