a1 唯一吗?tryselect * from tb a where not exists(select 1 from tb where a2=a.a2 and a3=a.a3 and a.a1>a.a1)
select * from tb a where not exists(select 1 from tb where a2=a.a2 and a3=a.a3 and a.a1>a.a1)
根据a1与a4的顺序,用case语句将其转为空字符串,查询必须加order by 才能保证a2,a3在第一行,空的在后边--设表名为t select a1,case when exists(select * from (select top 1 * from t where a2=a.a2 and a3=a.a3 order by a1,a4) b where a1=a.a1 and a4=a.a4) then a2 else '' end a2, case when exists(select * from (select top 1 * from t where a2=a.a2 and a3=a.a3 order by a1,a4) b where a1=a.a1 and a4=a.a4) then a3 else '' end a3, a4 from t a order by a1,a4
;with droptest as ( select ROW_NUMBER()over(PARTITION by a2,a3 order by (select 1))as row, a1,a2,a3 from dbo.test ) --删除 delete from droptest where ROW<>1 --查询 select * from droptest where row=1
select a1,case when exists(select * from
(select top 1 * from t where a2=a.a2 and a3=a.a3 order by a1,a4) b
where a1=a.a1 and a4=a.a4) then a2 else '' end a2,
case when exists(select * from
(select top 1 * from t where a2=a.a2 and a3=a.a3 order by a1,a4) b
where a1=a.a1 and a4=a.a4) then a3 else '' end a3,
a4
from t a
order by a1,a4
;with droptest as
(
select ROW_NUMBER()over(PARTITION by a2,a3 order by (select 1))as row,
a1,a2,a3
from dbo.test
)
--删除 delete from droptest where ROW<>1
--查询 select * from droptest where row=1