sql 查询求助各位大神

Question

本帖最后由 x2500969 于 2013-11-15 18:18:55 编辑

Answer 1

不好意思，重新描述一下，现只要c中相加结近100的优先排序------------------------------------------
如表table1中a              b                             c 
111          222                        15
113          222                        65
121          222                        35
125          222                        45
126          222                        15
.................................现要求选择a值中第二个数字相同然后c中值相加接近为100的优先的进行排序。输出格式为：a              b                 c            a            b         c          d
111         222             15          113        222      65        80
121         222             35          125        222      45        80

Answer 2

不好意思，重新描述一下，现只要c中相加结近100的优先排序------------------------------------------
如表table1中a              b                             c 
111          222                        15
113          222                        65
121          222                        35
125          222                        45
126          222                        15
.................................现要求选择a值中第二个数字相同然后c中值相加接近为100的优先的进行排序。输出格式为：a              b                 c            a1          b1       c1          d
111         222             15          113        222      65        80
121         222             35          125        222      45        80

Answer 3

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)
*/