Question

菜鸟求mysql的执行语句，要如下效果:

求出每个学生 最近7次考试总和，最近7次中的最高分，最近7次的最低分， 将所求值填到他们的最近一次的考试时间上
name score date lastsum7(最后7次总和) lastmax7（最后7次中最高分） lastmin7（最后7次中最低分）

Answer 1

UPDATE student
SET lastsum7 = (
SELECT
SUM(st1.ss1)
FROM
(
SELECT
s1.score ss1
FROM
(
SELECT
score
FROM
student
WHERE
NAME = '小东'
ORDER BY
date DESC
LIMIT 0,
7
) s1
) st1
),
lastmax7 = (
SELECT
MAX(st2.ss2)
FROM
(
SELECT
s2.score ss2
FROM
(
SELECT
score
FROM
student
WHERE
NAME = '小东'
ORDER BY
date DESC
LIMIT 0,
7
) s2
) st2
),
lastmin7 = (
SELECT
MIN(st3.ss3)
FROM
(
SELECT
s3.score ss3
FROM
(
SELECT
score
FROM
student
WHERE
NAME = '小东'
ORDER BY
date DESC
LIMIT 0,
7
) s3
) st3
)
WHERE
date = (
SELECT
MAX(dt.d)
FROM
(
SELECT
date d
FROM
student
WHERE
NAME = '小东'
) dt
)
AND NAME = '小东';

名字条件只能这么指定。
如果你想不指定，那就写过程，或者函数。用循环遍历。
student是表名。

CREATE PROCEDURE upscore ()
BEGIN
declare stName VARCHAR(64);
declare NameCursor cursor for select name from student GROUP BY name;
open NameCursor;
loop_label:LOOP
fetch NameCursor into stName;

UPDATE student
SET lastsum7 = (
SELECT
SUM(st1.ss1)
FROM
(
SELECT
s1.score ss1
FROM
(
SELECT
score
FROM
student
WHERE
NAME = stName
ORDER BY
date DESC
LIMIT 0,
7
) s1
) st1
),
lastmax7 = (
SELECT
MAX(st2.ss2)
FROM
(
SELECT
s2.score ss2
FROM
(
SELECT
score
FROM
student
WHERE
NAME = stName
ORDER BY
date DESC
LIMIT 0,
7
) s2
) st2
),
lastmin7 = (
SELECT
MIN(st3.ss3)
FROM
(
SELECT
s3.score ss3
FROM
(
SELECT
score
FROM
student
WHERE
NAME = stName
ORDER BY
date DESC
LIMIT 0,
7
) s3
) st3
)
WHERE
date = (
SELECT
MAX(dt.d)
FROM
(
SELECT
date d
FROM
student
WHERE
NAME = stName
) dt
)
AND NAME = stName;

end LOOP;
close NameCursor;
end;

这是存储过程，
直接生成过程。然后 CALL upscore();就行了。

追问

非常感谢， 真的非常感谢， 容我测试一下

追答

上边的是不用存储过程，直接用sql语句，这样的话你要处理哪个人所有指定人作为条件的都要变。
看你的表只有3个人好说。但是如果你是要处理n多个人，这样指定不现实。所以用下边的存储过程，无论多少人都给处理。