# 按B列相邻分组取A列最大行，并求分组后A的最大最小差，该如何实现呢？

happyhhb 2013-05-06 10:06:23

``````
f1   f2
1    c
12   a
3    b
11   a
4    b
5    a
2    b
7    c
8    d
9    c
10   c
6    a
``````

``````
f1   f2
1    c
2    b
3    b
4    b
5    a
6    a
7    c
8    d
9    c
10   c
11   a
12   a
``````

``````
f1   f2   sub
1    c    0   --f2相邻分组只有1行，所以为0
4    b    2   --f2相邻分组有3行，都是b，对应f1分别是2、3、4，则f1只留下最大的4，sub = 4-2 = 2
6    a    1   --f2相邻分组有2行，都是a，对应f1分别是5、6，则f1只留下最大的6，sub = 6-5 = 1
7    c    0   --f2相邻分组只有1行，所以为0
8    d    0   --f2相邻分组只有1行，所以为0
10   c    1   --f2相邻分组有2行，都是c，对应f1分别是9、10，则f1只留下最大的10，sub = 10-9 = 1
12   a    1   --f2相邻分组有2行，都是a，对应f1分别是11、12，则f1只留下最大的12，sub = 12-11 = 1
``````

...全文
179 点赞 收藏 7

7 条回复

PayneYu 2013-05-07

happyhhb 2013-05-06

``````Expr1000	f2
-5	a
2	b
8	c
0	d``````

dyf0130 2013-05-06
select max(f1) - min(f1),f2 from t group by f2 是这个意思吧

lily_083593 2013-05-06
create table a ( f1 int, f2 varchar(10) ) insert into a values(1,'c'), (12,'a'), (3,'b'), (11,'a'), (4,'b'), (5 ,'a'), (2,'b'), (7,'c'), (8,'d'), (9,'c'), (10,'c'), (6,'a' ) with #a as ( SELECT *,ROW_NUMBER() OVER ( ORDER BY row1 )AS ord FROM (select row1 ,F1,F2, isnull(row2,row1+1) as row2, val1 , isnull(val2,'#') as val2 from (SELECT F1 AS F1,ROW_NUMBER() OVER ( ORDER BY f1 )AS row1 , f2 AS val1 FROM a ) t1 LEFT JOIN (SELECT F1 AS F2,ROW_NUMBER() OVER ( ORDER BY f1 ) AS row2 , f2 AS val2 FROM a )t2 ON t1.row1 + 1 = t2.row2 )t where val1<>val2 ) select a.F1, a.val1, isnull(a.F1-b.F2,0) as result from #a a left join #a b on a.ord=b.ord+1 where a.val1<>a.val2

``````
create table hap
(f1 int, f2 varchar(5))

insert into hap
select 22, 'k' union all
select 1, 'c' union all
select 5, 'b' union all
select 11, 'c' union all
select 51, 'a' union all
select 7, 'b' union all
select 8, 'a' union all
select 10, 'a' union all
select 40, 'k' union all
select 17, 'd' union all
select 4, 'b' union all
select 57, 'a' union all
select 44, 'g'

select f1,f2 from hap order by f1
/*
f1          f2
----------- -----
1           c
4           b
5           b
7           b
8           a
10          a
11          c
17          d
22          k
40          k
44          g
51          a
57          a

(13 row(s) affected)
*/

with t0 as
(select f1,f2,row_number() over(order by f1) 'rn'
from hap
),
t1 as
(select a.f1,a.f2
from t0 a
left join t0 b on a.rn=b.rn-1
where b.rn is null or a.f2<>b.f2
),
t2 as
(select a.f1,a.f2
from t0 a
left join t0 b on a.rn=b.rn+1
where b.rn is null or a.f2<>b.f2
)
select c.f1,
c.f2,
isnull(c.f1-d.f1,0) 'sub'
from (select f1,f2,row_number() over(order by f1) 'rn' from t1) c
left join (select f1,f2,row_number() over(order by f1) 'rn' from t2) d
on c.rn=d.rn

/*
f1          f2    sub
----------- ----- -----------
1           c     0
7           b     3
10          a     2
11          c     0
17          d     0
40          k     18
44          g     0
57          a     6

(8 row(s) affected)
*/
``````

happyhhb 2013-05-06

``````f1    f2
22    k
1      c
5      b
11    c
51    a
7      b
8      a
10    a
40    k
17    d
4      b
57    a
44    g``````

``````f1    f2
1     c
4     b
5     b
7     b
8     a
10   a
11   c
17   d
22   k
40   k
44   g
51   a
57   a``````

``````f1    f2    sub
1     c      0     --f2相邻分组只有1行，所以为0
7     b     3     --f2相邻分组有3行，都是b，对应f1分别是4、5、7，则f1只留下最大的7，sub = 7-4 = 3
10   a     2     --f2相邻分组有2行，都是a，对应f1分别是10、8，则f1只留下最大的10，sub = 10-8 = 2
11   c     0     --f2相邻分组只有1行，所以为0
17   d     0     --f2相邻分组只有1行，所以为0
40   k     18    --f2相邻分组有2行，都是k，对应f1分别是22、40，则f1只留下最大的40，sub = 40-22 = 18
44   g     0     --f2相邻分组只有1行，所以为0
57   a     6     --f2相邻分组有2行，都是a，对应f1分别是51、57，则f1只留下最大的57，sub = 57-51 = 6``````

``````
create table hap
(f1 int, f2 varchar(5))

insert into hap
select 1, 'c' union all
select 12, 'a' union all
select 3, 'b' union all
select 11, 'a' union all
select 4, 'b' union all
select 5, 'a' union all
select 2, 'b' union all
select 7, 'c' union all
select 8, 'd' union all
select 9, 'c' union all
select 10, 'c' union all
select 6, 'a'

with t1 as
(select a.f1,
a.f2
from hap a
left join hap b on a.f1=b.f1-1
where b.f1 is null or a.f2<>b.f2
),
t2 as
(select f1,f2,row_number() over(order by f1) 'rn'
from t1
)
select c.f1,
c.f2,
isnull(c.f1-d.f1-1,0) 'sub'
from t2 c
left join t2 d on c.rn=d.rn+1

/*
f1          f2    sub
----------- ----- -----------
1           c     0
4           b     2
6           a     1
7           c     0
8           d     0
10          c     1
12          a     1

(7 row(s) affected)
*/
``````

Oracle

1.6w+

Oracle开发相关技术讨论

2013-05-06 10:06