MySQL查询最近两次尝试的平均值 [英] MySQL Query for Average of last 2 attempts

问题描述

我有一张桌子:

quiz userid  attempt grade

1      3        1     33

2      3        1     67

1      3        2     90

10     3        4     20

2      3        2     67

1      3        3     55

现在，对于第一个测验，用户进行了3次尝试，即(33，90，55)，现在我也需要该scormid和userid的最后2次尝试((90 + 55)/2)的平均值

Now For first quiz, an user gave 3attempts i.e., (33, 90, 55), Now I need the average for last 2 attempts ( (90 + 55)/2) for that scormid and userid too

现在，我想要最近两次尝试，即4和3，并且我希望这两个成绩的平均成绩分别为90和20 需要像这样的OP

Now, I want the last two attempts i.e., 4 and 3 and I want average grade of these 2 grades i.e, 90 and 20 Need the OP like

userid quiz No.of Attempts Grade

3      1         3           (90+55)/2 i.e., 72.5
3      2         2            (67+67)/2 i.e., 67
3      10        1               20

推荐答案

首先，您只需要选择尝试次数最多的两行(这是子查询的工作)，然后平均派生表中的成绩.

First you need to select just the two rows with highest attempts, which is what the subquery does, then average the grades from the derived table.

SELECT 
    AVG(grade) 
FROM (
      SELECT 
          grade 
      FROM 
          `table`
      ORDER BY attempt DESC 
      LIMIT 2
     ) t;

这将为您提供55.0000的结果，这是最后两次尝试的平均成绩90和20.

This will give you a result of 55.0000, which is the average of 90 and 20, the grades for the last two attempts.

这篇关于MySQL查询最近两次尝试的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！