mysql - 如何从mysql表中获取最后一条记录和倒数第二条记录

Question

从上图中，我有 n strip 有 cat_id 和 sub_cat_id 的记录，但在图像中只有两条记录。

所以我想获取 last 和 secondlast score_in_per 值，名为 lastScore 和 latetsScore..

我怎样才能检索到它..？

SELECT
(SELECT score_in_per FROM tbl_student_skill_score ORDER BY date DESC LIMIT 2,0)  as lastScore,
(SELECT score_in_per FROM tbl_student_skill_score ORDER BY date DESC LIMIT 1)  as latetsScore

我对这个复杂的 mysql 逻辑很陌生..这是我尝试过的..

示例:假设一个用户电子邮件地址是 inststudent@yopmail.com，进行相同的测试 2 次，并且测试与一个类别和子类别相关联。所以用户可以多次参加测试...

from that records i want to get the last two records percentage.

Answer 1

如果我理解正确的话；您想要选择特定学生参加的特定类型测试的两个最新结果。

您没有正确使用LIMIT子句。这是正确的语法:LIMIT {[offset,] row_count | row_count OFFSET 偏移}。另外，您完全省略了 where 子句。

所以查询应该是:

SELECT
(SELECT score_in_per FROM tbl_student_skill_score
  WHERE user_email = "email of the user you are interested in"
    AND cat_id = categoryOfTestOfInterest
    AND sub_cat_id = subcategoryOfTestOfInterest
  ORDER BY date DESC LIMIT 1, 1
)AS lastScore,
(SELECT score_in_per FROM tbl_student_skill_score
  WHERE user_email = "email of the user you are interested in"
    AND cat_id = categoryOfTestOfInterest
    AND sub_cat_id = subcategoryOfTestOfInterest
  ORDER BY date DESC LIMIT 1
)AS latetsScore;

如果学生每天可以多次参加测试(如您的图片所示)，那么您还应该按 id 排序(假设 id 总是更大以获取新结果)，或者更好的是，仅按 id 排序:

SELECT
(SELECT score_in_per FROM tbl_student_skill_score
  WHERE user_email = "email of the user you are interested in"
    AND cat_id = categoryOfTestOfInterest
    AND sub_cat_id = subcategoryOfTestOfInterest
  ORDER BY id DESC LIMIT 1, 1
)AS lastScore,
(SELECT score_in_per FROM tbl_student_skill_score
  WHERE user_email = "email of the user you are interested in"
    AND cat_id = categoryOfTestOfInterest
    AND sub_cat_id = subcategoryOfTestOfInterest
  ORDER BY id DESC LIMIT 1
)AS latetsScore;