--我想到3种方法
--测试数据
create table #T (sn varchar(5),A int, B int,C int,D int)
insert into #T
select '#1',4,3,6,9 union all
select '#2',1,2,6,7 union all
select '#3',2,2,4,0 union all
select '#4',9,4,7,8 union all
select '#5',6,2,1,8
--方法一:遇到B相等的,取sn最大的
select sn as ' ',A,B,C,D from #T T1
where not exists(select T2.sn from #T T2 where
T2.sn > T1.sn and T2.B = T1.B
)
/*
A B C D
----- ----------- ----------- ----------- -----------
#1 4 3 6 9
#4 9 4 7 8
#5 6 2 1 8
(3 row(s) affected)
*/
--方法二:遇到B相等的,取sn最小的
select sn as ' ',A,B,C,D from #T T1
where not exists(select T2.sn from #T T2 where
T2.sn < T1.sn and T2.B = T1.B
)
/*
A B C D
----- ----------- ----------- ----------- -----------
#1 4 3 6 9
#2 1 2 6 7
#4 9 4 7 8
(3 row(s) affected)
*/
--方法三:遇到B相等的,随机取出一个sn
select sn as ' ',A,B,C,D from #T T1
where T1.sn = (select top 1 T2.sn from #T T2 where
T2.B = T1.B order by newid()
)
/*
A B C D
----- ----------- ----------- ----------- -----------
#1 4 3 6 9
#4 9 4 7 8
#5 6 2 1 8 --B=2的是随机取得
select a,b,c,d from(
select top 1 * from 表 where id<'#2' order by id desc)a
union all
select a,b,c,d from(
select top 1 *,n=newid() from 表 where id<>'#2' order by n)a
union all
select a,b,c,d from(
select top 1 *,n=newid() from 表 where id<>'#2' order by n)a