我有一表数据,数据如下:
ME001 ME002 ME003
--------------------
10001 A B
10001 A A
10002 A V
10001 A T
--------------------可以用什么语句,只保留以下数据,其它数据删除。
ME001 ME002 ME003
--------------------
10001 A B
10002 A V
--------------------
ME001 ME002 ME003
--------------------
10001 A B
10001 A A
10002 A V
10001 A T
--------------------可以用什么语句,只保留以下数据,其它数据删除。
ME001 ME002 ME003
--------------------
10001 A B
10002 A V
--------------------
select 1 from tb where t.me001=me001 and t.me003>me003)
use City;
go
if object_id('[tb]') is not null drop table [tb]
create table [tb]
(
ME001 int,
ME002 nvarchar(20),
ME003 nvarchar(20)
)
insert into [tb]
select 10001,'A', 'B' union all
select 10001,'A', 'A' union all
select 10002,'A', 'V' union all
select 10001,'A', 'T'
go
select * from (select *,row_number()over(partition by ME001 order by getdate())as RN from tb) as A
where A.RN=1
drop table tb
/*
(4 行受影响)
ME001 ME002 ME003 RN
----------- -------------------- -------------------- --------------------
10001 A B 1
10002 A V 1(2 行受影响)*/
A.ME001,A.ME002,A.ME003即可。