34,590
社区成员
发帖
与我相关
我的任务
分享
--删除:
delete a from tablename as a where exists(select 1 from tablename where sheet_no=a.sheet_no and item_no=a.item_no and flow_id>a.flow_id)
--只时查询--用group by
select min(flow_id) as flow_id,sheet_no ,item_no from tablename group by sheet_no ,item_no
create table test(flow_id int,sheet_no varchar(30),item_no varchar(10))
go
insert into test values
(2014576,'DI0000061605254248','3002889'),
(2035489,'DI0000061605254248','3002889')
go
with m as
(select row_number() over(partition by sheet_no,item_no order by flow_id) rn,*
from test
)
delete m where rn > 1
go
select * from test
go
drop table test
go
(2 行受影响)
(1 行受影响)
flow_id sheet_no item_no
----------- ------------------------------ ----------
2014576 DI0000061605254248 3002889
(1 行受影响)