我有一个 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);更多推荐
选择没有空值的列中的最后一个值
发布评论