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 婆婆高寿祝福语大全简短