17,140
社区成员




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
Expr1000 f2
-5 a
2 b
8 c
0 d
有那么点意思,呵呵,但我想的效果是相邻分组,而且还需要列出f1中的相邻分组后分组中的最大值
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)
*/
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
貌似更麻烦了,呵呵,没办法,这比较接近我真实要做的案例,确实让人想破头了
谢谢各位指教 ^_^
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)
*/