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

Question

本帖最后由 happyhhb 于 2013-05-06 10:10:01 编辑

Answer 1

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

Answer 2

谢谢 di74jun，你语句的结果如下：
Expr1000 f2
-5 a
2 b
8 c
0 d
有那么点意思，呵呵，但我想的效果是相邻分组，而且还需要列出f1中的相邻分组后分组中的最大值

Answer 3

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

Answer 4

谢谢 ap0405140，请问如果f1并不是序列规律递增的，而是不规律递增的，“a.f1=b.f1-1”也许就不适用了，比如f1是精确到秒的时间字符串，因此还想问问，假设是下表这样的情况该怎么办呢？
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排序后结果如下：此时除了f1是因为排序不规律递增的，f2并没有什么规律可言
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
然后按f2相邻分组，也就是连续出现的作为分组，然后求分组后f1最大和最小的差值
结果如下：
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
貌似更麻烦了，呵呵，没办法，这比较接近我真实要做的案例，确实让人想破头了
谢谢各位指教 ^_^

Answer 5

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

Answer 6

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

Answer 7

用分析函数row_number()进行分组排序+model子句进行行间运算，很容易解决