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

happyhhb 2013-05-06 10:06:23

可能表述有些问题，呵呵
假设有表t如下

我想先按f1排序，如下：

然后按f2相邻分组，也就是连续出现的作为分组，然后求分组后f1最大和最小的差值
结果如下：



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

呵呵，不知道表述得是否清楚，貌似挺难的问题，想了蛮久，不知道怎样用简单的sql解决
谢谢各位大大的指教 ^_^

...全文

179 点赞收藏 7

写回复

7 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

PayneYu 2013-05-07

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

回复赞

happyhhb 2013-05-06

谢谢 di74jun，你语句的结果如下：

Expr1000	f2
-5	a
2	b
8	c
0	d

有那么点意思，呵呵，但我想的效果是相邻分组，而且还需要列出f1中的相邻分组后分组中的最大值

回复赞

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

回复赞

唐诗三百首 2013-05-06


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

谢谢 ap0405140，请问如果f1并不是序列规律递增的，而是不规律递增的，“a.f1=b.f1-1”也许就不适用了，比如f1是精确到秒的时间字符串，因此还想问问，假设是下表这样的情况该怎么办呢？

按f1排序后结果如下：此时除了f1是因为排序不规律递增的，f2并没有什么规律可言

然后按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

貌似更麻烦了，呵呵，没办法，这比较接近我真实要做的案例，确实让人想破头了谢谢各位指教 ^_^

回复赞

唐诗三百首 2013-05-06


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

回复赞