标题表述不太准,呵呵
这个问题该如何写sql解决呢?
对t1的f1,只对上下相连相同的分组,取分组中f2值最大的行,得到t2t1
id f1 f2
1 3 1
2 3 4
3 3 2
4 3 3
5 6 5
6 6 6
7 3 7
8 3 8
9 4 9t2
id f1 f2
2 3 4
6 6 6
8 3 8
9 4 9 谢谢各位大大指教 ^_^||
这个问题该如何写sql解决呢?
对t1的f1,只对上下相连相同的分组,取分组中f2值最大的行,得到t2t1
id f1 f2
1 3 1
2 3 4
3 3 2
4 3 3
5 6 5
6 6 6
7 3 7
8 3 8
9 4 9t2
id f1 f2
2 3 4
6 6 6
8 3 8
9 4 9 谢谢各位大大指教 ^_^||
id f1 f2 fz
1 3 1 1
2 3 4 1
3 3 2 1
4 3 3 1
5 6 5 2
6 6 6 2
7 3 7 3
8 3 8 3
9 4 9 4如果能增加辅助列fz,按照fz列分组,再获取id提取行数据,就可以达到目的了,不知道怎样才能实现这个功能
where f2=(select max(f2) from tb where f1=a.f1)
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[f1] int,[f2] int)
Insert #T1
select 1,3,1 union all
select 2,3,4 union all
select 3,3,2 union all
select 4,3,3 union all
select 5,6,5 union all
select 6,6,6 union all
select 7,3,7 union all
select 8,3,8 union all
select 9,4,9
Go
;with cte
as(
select
*,[id]-row as groups
from (Select *,ROW_NUMBER()over(partition by f1 order by id) as row
from #T1
)a
)
select ID,f1,f2 from cte as a where not exists(select 1 from cte where groups=a.groups and f1=a.f1 and f2>a.f2)
order by id
/*
ID f1 f2
2 3 4
6 6 6
8 3 8
9 4 9
*/
不给力啊,结果都是
6 6 6
8 3 8
9 4 9
select * from t1 a
where not exists(
select 1 from t1 b where f1=a.f1 and f2>a.f2 and not exists(
select 1 from t1 where id>a.id and id<b.id and f1<>a.f1 or id<a.id and id>b.id and f1<>a.f1)
)
Insert into t1
select 1,3,1 union all
select 2,3,4 union all
select 3,3,2 union all
select 4,3,3 union all
select 5,6,5 union all
select 6,6,6 union all
select 7,3,7 union all
select 8,3,8 union all
select 9,4,9
go
select * from t1 a
where not exists(
select 1 from t1 b where f1=a.f1 and f2>a.f2 and not exists(
select 1 from t1 where id>a.id and id<b.id and f1<>a.f1 or id<a.id and id>b.id and f1<>a.f1)
)
/*
id f1 f2
----------- ----------- -----------
2 3 4
6 6 6
8 3 8
9 4 9(4 行受影响)*/
go
drop table t1
;with cte
as
(Select *,ID-ROW_NUMBER()over(partition by f1 order by id) as groups from #T1 )
select ID,f1,f2 from cte as a where not exists(select 1 from cte where groups=a.groups and f1=a.f1 and f2>a.f2)
order by id
/*
ID f1 f2
2 3 4
6 6 6
8 3 8
9 4 9
*/
这样理解晴天兄弟可以简化一些,这样楼主能理确么?select * from t1 a
where not exists(
select 1 from t1 b where f1=a.f1 and f2>a.f2 and not exists(
select 1 from t1 where id>a.id and id<b.id )
)
第1个 not exists 是取f1相同f2最大的一条记录,第2个not exists是取范围 ID<b.ID and ID>a.ID中间的值过滤。