sql实现一条数据与前一条数据的比较

sql实现一条数据与前一条数据的比较

    最近项目中遇到一些数据统计的需求，具体需求就是要统计对于某一列较之前相同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 beforemaxidFROM 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 beforemaxidFROM student_score currentscore INNER JOIN student_score beforescore ON currentscore.name = beforescore.name WHERE beforescore.id < currentscore.id) b ON a.id = b.currentidLEFT JOIN student_score cON b.beforemaxid = c.idWHERE a.score > c.scoreGROUP BY exam_id

    这里主要讲了一下面临的问题，以及思考的过程，欢迎大家来批评指正，或者可以有更好的实现方案。