查询固定行数数据的方法

[myricemeli]

请问在数据库中如何查询上图这样的情况？

[咔咔-]

在哪看的这种需求呀！项目中不会用的

[土豆不太熟]

1. 这个需求不知道是不是XY问题； 2. 如果单单是这个问题的话，在程序里实现起来比较合适，给写好的SQL传一个起始id、一个结束id，返回最大、最小值等比较灵活； 3. 如果需要在SQL中实现的话，建议用另外一张表保存查出来的这些数据，方便以后再次查询，我随意建了一些数据，查询结果如下，不知道是不是楼主想要的结果： 原数据： 查询的结果： 涉及存储过程：

BEGIN
	DECLARE i int DEFAULT 1;
	DECLARE max_ ,min_ ,sum_ ,avg_ int ;
	DECLARE ids_ VARCHAR(20) ;
	DECLARE count int DEFAULT 0;
	SELECT count(*) into count from t_temp ;
	WHILE i+5 <= (count+1) DO
		SELECT MAX(t.`value`),MIN(t.`value`),SUM(t.`value`),AVG(t.`value`)from t_temp t WHERE t.id in (i,i+1,i+2,i+3,i+4) into max_,min_,sum_,avg_;
		SET ids_ = CONCAT_WS(",",i,i+1,i+2,i+3,i+4);
		insert into t_temp_view(ids,max,min,sum,avg) values(ids_,max_,min_,sum_,avg_);
		SET i = i+1;
	end WHILE;
end

[myricemeli]

实际要求比这个还麻烦，我只是简单举例叙述问题。

[ACMAIN_CHM]

mysql> select * from myricemeli;
+----+-----------+
| id | col_value |
+----+-----------+
|  1 |        35 |
|  2 |        68 |
|  3 |        45 |
|  4 |        71 |
|  5 |        59 |
|  6 |        63 |
|  7 |        25 |
|  8 |        46 |
|  9 |        58 |
+----+-----------+
9 rows in set (0.01 sec)

mysql> select id, col_value,
    ->  (select sum(col_value) from (select col_value from myricemeli where id>=t.id order by id limit 5) b) as s,
    ->  (select avg(col_value) from (select col_value from myricemeli where id>=t.id order by id limit 5) b) as a
    -> from myricemeli t
    -> where (select count(*) from myricemeli where id>=t.id)>=5;
+----+-----------+------+---------+
| id | col_value | s    | a       |
+----+-----------+------+---------+
|  1 |        35 |  278 | 55.6000 |
|  2 |        68 |  306 | 61.2000 |
|  3 |        45 |  263 | 52.6000 |
|  4 |        71 |  264 | 52.8000 |
|  5 |        59 |  251 | 50.2000 |
+----+-----------+------+---------+
5 rows in set (0.00 sec)

mysql>

[土豆不太熟]

引用 4 楼 ACMAIN_CHM 的回复:

mysql> select * from myricemeli;
+----+-----------+
| id | col_value |
+----+-----------+
|  1 |        35 |
|  2 |        68 |
|  3 |        45 |
|  4 |        71 |
|  5 |        59 |
|  6 |        63 |
|  7 |        25 |
|  8 |        46 |
|  9 |        58 |
+----+-----------+
9 rows in set (0.01 sec)

mysql> select id, col_value,
    ->  (select sum(col_value) from (select col_value from myricemeli where id>=t.id order by id limit 5) b) as s,
    ->  (select avg(col_value) from (select col_value from myricemeli where id>=t.id order by id limit 5) b) as a
    -> from myricemeli t
    -> where (select count(*) from myricemeli where id>=t.id)>=5;
+----+-----------+------+---------+
| id | col_value | s    | a       |
+----+-----------+------+---------+
|  1 |        35 |  278 | 55.6000 |
|  2 |        68 |  306 | 61.2000 |
|  3 |        45 |  263 | 52.6000 |
|  4 |        71 |  264 | 52.8000 |
|  5 |        59 |  251 | 50.2000 |
+----+-----------+------+---------+
5 rows in set (0.00 sec)

mysql>

好厉害，我想过一条SQL来实现的，但是我不会写SQL，所以才写了存储过程； 不知道为什么我执行了你的SQL，只是换了个表名，执行报错，希望大佬赐教： 我的表结构，只有一个id， value，表名是t_temp，为什么t.id会报错呢

select id, value,
(select sum(value) from (select value from t_temp where id >= t.id order by id limit 5) b) as s,
(select avg(value) from (select value from t_temp where id >= t.id order by id limit 5) b) as a 
from t_temp t where (select count(*) from t_temp where id>=t.id)>=5;