-- 是不是这样?
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)
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)
有没有其它方法?
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' union ALL
select 7, 'x' union all
select 8, 'y' union all
select 9, 'y' union all
select 10, 'y'增加几条记录,还是刚才的规则。
结果:1, 'x'
6, 'y'
10, 'y'能否不用union all得到上面的结果?
1 x
2 x
3 y
4 y
5 y
6 y
7 x
8 x
9 y
10 y结果是什么?
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'