原始数据是这样类型的
create table [tb]([code] varchar,[value] float)
insert [tb]
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 2,4 union all
select 2,4 union all
select 3,6 union all
select 3,6 union all
select 3,6 union all
select 2,4 union all
select 2,4 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 3,5 union all
select 3,5 union all
select 3,5 union all
select 2,2 union all
select 2,2 union all
要得到结果
code value
1 2
2 4
3 6
2 4
1 2
3 5
2 2
各位大侠这样的sql server2005 下的sql查询要怎么写呢~。
sql 分组 查询
------解决方案--------------------
distinct
------解决方案--------------------
with a as(
select row_number() over(order by getdate()) a,* from tb)
)
select code,value from a
where a=1
union all
select code,value from a b
where exists(select 1 from a where b.a-1=a
and b.code!=code and b.value!=value)
------解决方案--------------------
with test as
(select rb=row_number() over (order by getdate()),* from tb)
select code,value from test where rb not in
(select b.rb from test a,test b where a.rb=b.rb+1 and a.code=b.code and a.value=b.value)
------解决方案--------------------
create table #tb([code] int,[value] float)
insert #tb
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 2,4 union all
select 2,4 union all
select 3,6 union all
select 3,6 union all
select 3,6 union all
select 2,4 union all
select 2,4 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 1,2 union all
select 3,5 union all
select 3,5 union all
select 3,5 union all
select 2,2 union all
select 2,2
;with ceb as
(
select ROW_NUMBER() over(order by getdate()) as id,* from #tb
)
,ceb2 as
(
select a.code,a.value from ceb a where a.id=1
union all
select a.code,a.value from ceb a where exists
(select 1 from ceb b where a.id-1=b.id and b.code!=a.code and b.value!=a.value)
)
select * from ceb2