时间戳

首页 > 解决方案 > 根据几个标准选择最后一条记录

sql - 根据几个标准选择最后一条记录

问题描述

前

+--------+--------+---------+-------+------+
| RowNum | Status | Remarks | SetNo |      |
+--------+--------+---------+-------+------+
|      1 | Q      |         | Set 1 | Want |
|      2 | Q      |         | Set 1 | Want |
|      3 | Q      |         | Set 1 | Want |
|      4 | Q      |         | Set 1 | Want |
|      5 | W      |         | Set 1 | Want |
|      1 | W      | abc     | Set 2 |      |
|      2 | W      | abc     | Set 2 |      |
|      3 | W      | abc     | Set 2 |      |
|      4 | W      | abc     | Set 2 | Want |
|      1 | Q      |         | Set 3 | Want |
|      2 | w      | abc     | Set 3 |      |
|      3 | w      | abc     | Set 3 | Want |
+--------+--------+---------+-------+------+

如何根据 Rownum=lastnumber 和 setno 选择 Status=Q 和 Status=W？预期结果是“想要”的行是我需要的。那些空的，将被删除

试过：

select *
from mytable
where (RowNum != (select max(RowNum) from mytable) and status = 'W')

标签： sqlsql-serversubquerygreatest-n-per-group

解决方案

我知道对于每个setno，您都需要所有“Q”和最新的“W”。如果是这样，您可以使用这样的窗口函数：

select *
from (
    select t.*, 
        row_number() over(partition by setno, status order by rownum desc) rn
    from mytable t
) t
where rn = 1 or status = 'Q'

推荐阅读