如何找到具有特定alpabet但具有相同id的最新2个结果

kqlmhetl 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(348)

原始数据

+---------+--------+------------+
|   ID    | STATUS |    DATE    |
+---------+--------+------------+
| ABC1234 | P      | 2020-01-01 |
| ABC1234 | P      | 2020-01-02 |
| ABC1234 | B      | 2020-01-03 |
| ABC1234 | A      | 2020-01-04 |
| ABC1234 | A      | 2020-01-05 |
| BCD     | P      | 2020-01-01 |
| BCD     | P      | 2020-01-02 |
| BCD     | P      | 2020-01-03 |
| BCD     | B      | 2020-01-04 |
| BCD     | A      | 2020-01-05 |
| 12XY    | P      | 2020-01-01 |
| 12XY    | P      | 2020-01-02 |
| 12XY    | P      | 2020-01-03 |
| 12XY    | B      | 2020-01-04 |
| 12XY    | B      | 2020-01-05 |
+---------+--------+------------+

我想要的结果

+-----+--------+------------+
| ID  | STATUS |    DATE    |
+-----+--------+------------+
| BCD | B      | 2020-01-04 |
| BCD | A      | 2020-01-05 |
+-----+--------+------------+

如何找到最新记录状态为a，第二个最新记录状态为b的结果？
在下面试用过，但仍然不知道如何获取id=bcd

select ROW_NUMBER() 
        OVER (PARTITION BY ID ORDER BY date2 DESC) AS rownum
         ,*
         into testingtable
         from mytable
         WHERE status in ('b', 'a')

sql window-functions sql-server greatest-n-per-group tsql

来源：https://stackoverflow.com/questions/62066063/how-to-find-latest-2-result-with-specific-alpabet-but-with-same-id

2条答案

按热度按时间

csbfibhn1#

如何查找最新记录 Status=A ，和第二个最新记录 Status=B ?
您可以使用窗口函数和一些布尔逻辑：

select id, status, date
from (
    select t.*, row_number() over(partition by status order by date desc) rn
    from mytable t
    where status in ('A', 'B')
) t
where (status = 'A' and rn = 1) or (status = 'B' and rn = 2)

赞(0）回复(0）举报 2021-08-13

tvmytwxo2#

从子查询中删除where子句并使用布尔逻辑：

select t.*
from (select t.*, row_number() over(partition by id order by date desc) as seq
      from table t
      ) t
where (status = 'A' and seq = 1) or (status = 'B' and seq = 2) ;

赞(0）回复(0）举报 2021-08-13

我来回答

如何找到具有特定alpabet但具有相同id的最新2个结果

2条答案

相关问题

热门标签

最新问答