最近项目中遇到一些数据统计的需求,具体需求就是要统计对于某一列较之前相同id的一条数据变更的数目。描述起来可能相对抽象,我们举一个简单的例子:
对于一个记录学生分数(student_score)的表,我们要统计每一次考试,有多少学生取得了进步。
student_score
id name score exam_id
1 张三 86 1
2 李四 74 1
3 王五 58 1
4 张三 78 2
5 李四 87 2
6 王五 90 2
7 张三 89 3
8 李四 87 3
9 王五 89 3
我们想要的到的结果是:
exam_id count
2 2
3 1
其中exam_id为2的两条记录为:
5 李四 87 2
6 王五 90 2
exam_id为3的一条记录为:
7 张三 89 3
这个问题看上去相对比较复杂,我们需要去对比每个学生每一次考试与他前一次考试的比分,然后进行统计,因此基于以上问题我们先考虑我们需要找出学生每次的考试id与他对应的前一次考试的id。当这个考虑过程中,当然我们不能仅仅用倍数关系来考虑,因为若我们出现了某位同学中途加入或者某位同学中间辍学了,这样会影响我们的统计,因此我们写如下sql:
SELECT currentscore.id AS currentid,
MAX(beforescore.id) AS beforemaxid
FROM student_score currentscore
INNER JOIN student_score beforescore
ON currentscore.name = beforescore.name
WHERE beforescore.id < currentscore.id
如上sql我们可以实现查找每一个id对应的前一次操作的id,然后我们通过对比着两次id之间的不同来进行统计,最终得到如下sql
SELECT exam_id,
COUNT(id)
FROM student_score a
RIGHT JOIN (
SELECT currentscore.id AS currentid,
MAX(beforescore.id) AS beforemaxid
FROM student_score currentscore
INNER JOIN student_score beforescore
ON currentscore.name = beforescore.name
WHERE beforescore.id < currentscore.id
) b
ON a.id = b.currentid
LEFT JOIN student_score c
ON b.beforemaxid = c.id
WHERE a.score > c.score
GROUP BY exam_id
这里主要讲了一下面临的问题,以及思考的过程,欢迎大家来批评指正,或者可以有更好的实现方案。