按B列相邻分组取A列最大行，并求分组后A的最大最小差，该如何实现呢？本帖最后由 happyhhb 于 2013-05-06 10:
按B列相邻分组取A列最大行，并求分组后A的最大最小差，该如何实现呢？
本帖最后由 happyhhb 于 2013-05-06 10:10:01 编辑 可能表述有些问题，呵呵
假设有表t如下
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排序，如下：
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
然后按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解决
谢谢各位大大的指教 ^_^
[解决办法]
select max(f1) - min(f1),f2 from t group by f2
是这个意思吧
[解决办法]
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)
*/
[解决办法]
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)
*/
[解决办法]
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
[解决办法]
用分析函数row_number()进行分组排序+model子句进行行间运算，很容易解决