求一条排序筛选SQL语句的写法？

[flyseagod]

将表中的数据按时间排序，然后取出前后两条其中指定字段记录不同的SQl语句。比如说按排序后一个字段的记录是a a a b c c ，那么我要得到的是a b c ，请各位大侠帮忙。

[-狙击手-]

select *
from ta a
where exists(select 1 from ta where id = a.id - 1 and col != a.col)
and
exists(select 1 from ta where id = a.id + 1 and col != a.col)

[flyseagod]

[Quote=引用 1 楼 happyflystone 的回复:]
select *
from ta a
where exists(select 1 from ta where id = a.id - 1 and col != a.col)
and
exists(select 1 from ta where id = a.id + 1 and col != a.col)
[/Quote]
这个好像不能得到想要结果

[ChinaJiaBing]

declare @tb table (name nvarchar(10),时间 datetime)
insert into @tb select 'a','2009-5-21'
      union all select 'a','2009-5-22'
      union all select 'a','2009-5-23'
      union all select 'b','2009-5-24'
      union all select 'c','2009-5-25'
      union all select 'c','2009-5-26'
--select  *,ID=ROW_NUMBER()over( partition by name order by 时间 desc) from @tb order by 时间 desc
select top 2 name,时间 from(     
select  *,ID=ROW_NUMBER()over( partition by name order by 时间) from @tb) a where ID=1 
union 
select top 2 name,时间 from (
select top 100 *,ID=ROW_NUMBER()over( partition by name order by 时间 desc) from @tb order by 时间 desc) b where ID=1


name       时间
---------- -----------------------
a          2009-05-21 00:00:00.000
b          2009-05-24 00:00:00.000
c          2009-05-26 00:00:00.000

(3 行受影响)

[百年树人]

select 
  *
from 
  tb t
where
  not exists(select 1 from tb where col=t.col and 时间<t.时间)

[Zoezs]

select * from tb a where
  not exists(select 1 from tb where col=a.col and 时间<a.时间)

[flyseagod]

[Quote=引用 2 楼 flyseagod 的回复:]
引用 1 楼 happyflystone 的回复:
select *
from ta a
where exists(select 1 from ta where id = a.id - 1 and col != a.col)
and
exists(select 1 from ta where id = a.id + 1 and col != a.col)

这个好像不能得到想要结果
[/Quote]
把and改成or后可以，最好是能按时间排序

[ks_reny]

4楼的效率高。

[ch315537]

[Quote=引用 3 楼 ChinaJiaBing 的回复:]
SQL codedeclare@tbtable(namenvarchar(10),时间datetime)insertinto@tbselect'a','2009-5-21'unionallselect'a','2009-5-22'unionallselect'a','2009-5-23'unionallselect'b','2009-5-24'unionallselect'c','2009-5-25'unionallselect'c','2009-5-26'--select *,ID=ROW_NUMBER()over( partition by name order by 时间 desc) from @tb order by 时间 descselecttop2name,时间from(select*,ID=ROW_NUMBER()over( partitionbynam…
[/Quote]
学习学习

[lihan6415151528]

select  * from  tb t where not exists(select 1 from tb where col=t.col and 时间<t.时间)