我正在使用SQL Server,并且正在尝试提取某些数据。但是,一些行会弹出重复的订单号,其中一行#为null,而另一行具有正确的值。
棘手的是,还有其他行号为null,如果我要过滤line_id is not null,那么我将排除所有值为null的有效订单号。我会使用case语句吗?子查询?我不知所措。
这是我的代码的一个精简版本,我的意思是:
select
order_number
line_number
from table_1 发布于 2020-01-24 03:22:18
使用NOT EXISTS
select t.*
from tablename t
where t.line_number is not null
or not exists (
select 1 from tablename
where order_number = t.order_number and line_number is not null
)或使用ROW_NUMBER()窗口函数:
select t.order_number, t.line_number
from (
select *,
row_number() over (partition by order_number order by case when order_number is not null then 1 else 2 end) rn
from tablename
) t
where t.rn = 1https://stackoverflow.com/questions/59885654
复制相似问题