选择没有空值的列中的最后一个值 [英] Selecting last value in a colum without null value

问题描述

我有一个 SQL SELECT 语句情况如下:

I have a SQL SELECT Statement situation as follows:

我想选择并获取带有 Aquila's Test2 标记的行，即 30 作为标记列下的最后一个值，并省略空行.换句话说，我想通过 Test# 对学生进行分组并选择不为空的最新测试标记.数据应按 Test# DESC 顺序排序.

I want to select and get the row with Aquila's Test2 mark which is 30 as the last value under the mark column and omit the null rows. In other words, I want to group the students by Test# and select the latest test mark that is not null. Data should be Sorted by Test# DESC Order.

我如何实现这一目标?请帮忙.

How do I achieve this? Please help.

我的桌子是这样的:

+------------+-------------+
ID| Test# | Student | Mark |
+------------+-------------+
1 | Test1 | Aquila  | 20   |
2 | Test1 | Peter   | 30   |
3 | Test1 | Jack    | 40   |
4 | Test2 | Aquila  | 30   |
5 | Test2 | PETER   | 40   |
6 | Test2 | Jack    | 50   |
7 | Test3 | Aquila  | NULL |
8 | Test3 | Peter   | 50   |
9 | Test3 | Jack    | 60   |
+------------+-------------+

我希望输出是:

+------------+-------------+
ID| Test# | Student | Mark |
+------------+-------------+
4 | Test2 | Aquila  | 30   |
8 | Test3 | Peter   | 50   |
9 | Test3 | Jack    | 60   |
+------------+-------------+

推荐答案

我终于按照如下方式自己制定了 select 语句并且它起作用了.如果有人遇到类似问题，这里是工作代码.

I finally worked out the select statement myself as follows and it worked. If someone out there has a similar problem, here is the working code.

SELECT * FROM table 
WHERE ID IN (
SELECT MAX(ID) 
FROM table WHERE mark IS NOT NULL
GROUP BY Test# DESC);

这篇关于选择没有空值的列中的最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！