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)
热门推荐
10 祝福语赠言给老师简短
11 新郎表白成语祝福语简短
12 男士送花祝福语大全简短
13 姐姐生日祝福语漂亮简短
14 简短祝福语大全暖心
15 描写周末的祝福语简短
16 舅妈生日红包祝福语简短
17 爸爸爱你祝福语大全简短
18 宝宝周岁可乐祝福语简短