22,209
社区成员
发帖
与我相关
我的任务
分享
create table table1
(a int,b int,c int)
insert into table1
select 111,222,15 union all
select 113,222,65 union all
select 121,222,35 union all
select 125,222,45 union all
select 126,222,15
select a,b,c,a1,b1,c1,d
from
(select x.a,x.b,x.c,
y.a 'a1',y.b 'b1',y.c 'c1',
x.c+y.c 'd',
row_number() over(partition by substring(rtrim(x.a),2,1)
order by abs(100-(x.c+y.c))) 'rn'
from table1 x
cross join table1 y
where substring(rtrim(x.a),2,1)=substring(rtrim(y.a),2,1)
and not(x.a=y.a and x.b=y.b and x.c=y.c)) t
where t.rn=1
/*
a b c a1 b1 c1 d
----------- ----------- ----------- ----------- ----------- ----------- -----------
111 222 15 113 222 65 80
121 222 35 125 222 45 80
(2 row(s) affected)
*/