根据条件获取最新行

xam8gpfp 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(245)

我有一张table material --这是小提琴

+--------+-----+-------------------+----------------+-----------+          
| ID     | REV | name              | Description    | curr      |
+--------+-----+-------------------+----------------+-----------+
| 211-32 | 001 | Screw 1.0         | Used in MAT 1  | READY     |
| 211-32 | 002 | Screw 2 plus      | can be Used-32 | WITHDRAWN |
| 212-41 | 001 | Bolt H1           | Light solid    | READY     |
| 212-41 | 002 | BOLT H2+Form      | Heavy solid    | READY     |
| 101-24 | 001 | HexHead 1-A       | NOR-1          | READY     |
| 101-24 | 002 | HexHead Spl       | NOR-22         | READY     |
| 423-98 | 001 | Nut Repair spare  | NORM1          | READY     |
| 423-98 | 002 | Nut Repair Part-C | NORM2          | WITHDRAWN |
| 423-98 | 003 | Nut SP-C          | NORM2+NORM1    | NULL      |
| 654-01 | 001 | Bar               | Specific only  | WITHDRAWN |
| 654-01 | 002 | Bar rod-S         | Designed+Spe   | WITHDRAWN |
| 654-01 | 003 | Bar OPG           | Hard spec      | NULL      |
+--------+-----+-------------------+----------------+-----------+

这里每个id可以有多个修订。我想采取最新的修订（即最高的001002003等）。但如果最新版本 curr 作为其中之一 NULL （字符串）或 WITHDRAWN 然后我取上一个版本和它对应的值。即使是这样 curr 是 NULL 或者 WITHDRAWN 我得再看一遍以前的版本。如果所有的修订都有相同的问题，那么我们可以忽略它。所以预期的输出是

+--------+-----+------------------+---------------+-------+
| ID     | REV | name             | Description   | curr  |
+--------+-----+------------------+---------------+-------+
| 211-32 | 001 | Screw 1.0        | Used in MAT 1 | READY |
| 212-41 | 002 | BOLT H2+Form     | Heavy solid   | READY |
| 101-24 | 002 | HexHead Spl      | NOR-22        | READY |
| 423-98 | 001 | Nut Repair spare | NORM1         | READY |
+--------+-----+------------------+---------------+-------+

我试过下面的代码，但我不知道如何采取以前的修订。

with cte as (
select *,dense_rank() over (partition by id order by rev desc) as DR ,
lead(curr) over (partition by id order by rev desc) LEAD_CURR
from material )
select * from cte where DR = 1 and curr='READY'
union all
select * from cte where LEAD_CURR='READY' and DR=2
union all
select * from cte where LEAD_CURR='READY' and DR=3

sql sql-server sql-server-2012

来源：https://stackoverflow.com/questions/63152913/get-the-latest-row-based-on-condition

1条答案

按热度按时间

o2rvlv0m1#

这听起来像是过滤然后计算行号：

select m.*
from (select m.*,
             row_number() over (partition by id order by rev desc) as seqnum
      from material m
      where curr is not null and curr not in ( 'WITHDRAWN', 'NULL' ) 
     ) m
where seqnum = 1;

也可以使用相关子查询执行此操作：

select m.*
from material m
where m.rev = (select max(m2.rev)
               from material m2
               where m2.id = m.id and
                     curr is not null and curr not in ( 'WITHDRAWN', 'NULL' ) 
              );

这是一把小提琴。
注意：存储字符串是非常不传统的 'NULL' 因为这很容易与sql“常量”混淆 NULL .
另外，你的问题特别提到 'WITHDRAWN' 以及 NULL ，但它没有说明允许哪些其他值。显然，上述查询中的逻辑可能等价于 curr = 'READY' 你可以用这个。上面的逻辑符合你对问题的描述。

赞(0）回复(0）举报 2021-07-29

我来回答

根据条件获取最新行

1条答案

相关问题

热门标签

最新问答