sql基于其他行值排除行

8zzbczxx 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(299)

LINE_INVOICE    PAYMENT_SOURCE    SOURCE_PMT_ID                     
-1              Payment Received    7369442                                         
1               Payment Received    7369442                                         
2               Payment Received    7369442                                         
3               Payment Received    7369442                                         
4               Payment Received    7369442                                         
5               Payment Received    7369442                                         
6               Payment Received    7369442                                         
7               Payment Received    7369442                                         
8               Payment Received    7369442                                         
9               Payment Received    7369442                                         
10              Payment Received    7369442                                         
11              Payment Received    7369442                                         
12              Payment Received    7369442

我想删除'-1'行，只要“行发票列”中有任何其他数字。这对于每个源付款id都是特定的。如果没有“1”行，则返回-1。如何在我的sql查询中捕获它？

sql window-functions where-clause oracle

来源：https://stackoverflow.com/questions/63063256/sql-exclude-row-based-on-other-rows-value

3条答案

按热度按时间

ui7jx7zq1#

我怀疑您需要窗口函数：

select *
from (
    select 
        t.*,
        max(case when line_invoice <> -1 then 1 else 0 end) 
            over(partition by source_pmt_id) flag
    from mytable t
) t
where line_invoice <> -1 or flag = 0

Windows max() 在子查询中，检查是否至少存在一行具有相同的 source_pmt_id 和一个 line_invoice 值而不是 -1 . 外部查询使用该信息筛选出 line_invoice 有价值 -1 ，同时保留那些没有其他值的 source_pmt_id .

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

xpszyzbs2#

单向窗口功能：

select t.*
from (select t.*, max(line_invoice) over (partition by SOURCE_PMT_ID) as max_line_invoice
      from t
     ) t
where line_invoice > -1 or max_line_invoice = -1;

或者，如果没有子查询：

select t.*
from t
order by row_number() over (partition by source_pmt_id
                            order by case when line_invoice <> -1 then 1 else 2 end
                           )
fetch first 1 row with ties;

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

qaxu7uf23#

假设-1是最低的数字，并且第1行从那里开始上升：

select * 
from table1 tab
where LINE_INVOICE >= case when (select max(line_invoice) 
                                from table1 tb_
                                where tb_.payment_source = tab.payment_source
                                 and tb_.source_pmt_id = tab.source_pmt_id) = -1 then -1 else 0 end

这把小提琴展示了它的工作原理。

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

我来回答

sql基于其他行值排除行

3条答案

相关问题

热门标签

最新问答