mysql - 选择最新的行以及恰好 2 行具有一个条件的行和 2 行具有不同条件的行

Question

我有一个名为student_grades的表

╔════╤═══════╤═══════╤═════════════════════╗
║ id │ name  │ grade │ date_added          ║
╠════╪═══════╪═══════╪═════════════════════╣
║ 1  │ bob   │ 23    │ 2019-10-01 14:25:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 2  │ james │ 45    │ 2019-10-02 17:31:27 ║
╟────┼───────┼───────┼─────────────────────╢
║ 3  │ mike  │ 42    │ 2019-10-03 18:08:13 ║
╟────┼───────┼───────┼─────────────────────╢
║ 4  │ bob   │ 68    │ 2019-10-04 02:00:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 5  │ mike  │ 83    │ 2019-10-04 09:28:43 ║
╟────┼───────┼───────┼─────────────────────╢
║ 6  │ bob   │ 93    │ 2019-10-04 11:42:00 ║
╟────┼───────┼───────┼─────────────────────╢
║ 7  │ james │ 98    │ 2019-10-05 14:51:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 8  │ steph │ 72    │ 2019-10-05 15:31:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 9  │ john  │ 78    │ 2019-10-05 16:33:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 10 │ john  │ 74    │ 2019-10-05 17:42:23 ║
╟────┼───────┼───────┼─────────────────────╢
║ 10 │ nick  │ 92    │ 2019-10-05 17:59:12 ║
╚════╧═══════╧═══════╧═════════════════════╝

我使用此语句来获取学生的最新记录。例如。 James 有 2 条记录。一个带有 id 2 和一个带有 id 7。所以我想要 id 7 的，因为 id 更大。

然后我随机化这些行，它会返回我

╔════╤═══════╤═══════╤═════════════════════╗
║ id │ name  │ grade │ date_added          ║
╠════╪═══════╪═══════╪═════════════════════╣
║ 7  │ james │ 86    │ 2019-10-05 12:11:20 ║
╟────┼───────┼───────┼─────────────────────╢
║ 5  │ mike  │ 83    │ 2019-10-04 09:28:43 ║
╚════╧═══════╧═══════╧═════════════════════╝

声明:

SELECT s1.*
FROM student_grade s1
JOIN (SELECT name, MAX(id) AS id
      FROM student_grade
      GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
ORDER BY RAND()
LIMIT 2;

我的问题是，如何选择2个得分在70到80之间的学生(随机选择)和2个得分在90到99之间的学生(随机选择)的最新记录。

SELECT s1.*
FROM student_grade s1
JOIN (SELECT name, MAX(id) AS id
      FROM student_grade
      WHERE (grade >= 70 and grade <= 80) or (grade >= 90 and grade <= 99)
      GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
ORDER BY RAND()
LIMIT 4;

但是如果我执行上面的语句，它可能会给我 3 个得分为 70-80 的学生和 1 个得分为 90-99 的学生。我想要正好 2 名 70-80 的学生(随机选择)和正好 2(随机选择)的 90-99 学生。我怎样才能做到这一点？

Answer 1

您可以将两个现有查询UNION ALL组合在一起以获得您想要的结果:

(SELECT s1.*
 FROM student_grade s1
 JOIN (SELECT name, MAX(id) AS id
       FROM student_grade
       WHERE grade BETWEEN 70 AND 80
       GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
 ORDER BY RAND()
 LIMIT 2)
UNION ALL
(SELECT s1.*
 FROM student_grade s1
 JOIN (SELECT name, MAX(id) AS id
       FROM student_grade
       WHERE grade BETWEEN 90 AND 99
       GROUP BY name) s2 ON s2.name = s1.name AND s2.id = s1.id
 ORDER BY RAND()
 LIMIT 2)

Demo on dbfiddle

我们可以使用UNION ALL，因为我们知道不会有任何重复，因为等级范围不同。