id co1
1 x
2 x
3 y
4 y
5 y
6 y
如何用select 的方法得到结果
id co1
1 x
6 y
规则是1.等于y的行取last，last y 上面的y都不要。
2.y上面的第一个x不要
------解决方案--------------------
-- 是不是这样？
declare @temp table (d int, col varchar(1))
insert @temp
select 1, 'x ' union all
select 2, 'x ' union all
select 3, 'y ' union all
select 4, 'y ' union all
select 5, 'y ' union all
select 6, 'y '
select * from @temp a where d in (select top 1 d from @temp where col = 'x ' and col = a.col order by d) union all
select * from @temp a where d in (select top 1 d from @temp where col = 'y ' and col = a.col order by d desc)
------解决方案--------------------
select a.* from # a left join # b on a.id=b.id-1
where (a.col= 'x ' and b.col= 'x ')
or (a.col <> 'y ' and b.col <> 'y ')
or (a.col= 'y ' and b.col= 'x ')
or (a.col= 'y ' and b.col is null)
drop table #
create table #(id int ,col char(1))
insert into #
select 1 , 'x ' union all
select 2 , 'x ' union all
select 3 , 'y ' union all
select 4 , 'y ' union all
select 5 , 'y ' union all
select 6 , 'y ' union all
select 7 , 'x ' union all
select 8 , 'x ' union all
select 9 , 'y '