/*************無續取某行上一條記錄****************/
/*測試表*/
create table  #a (a int,b int,c char(1))
insert into #a select 3,3,'a'
insert into #a select 2,3,'b'
insert into #a select 1,3,'c'
insert into #a select 5,3,'d'
insert into #a select 4,3,'e'
insert into #a select 6,3,'o'
insert into #a select 7,3,'x'
insert into #a select 100,3,'z'
insert into #a select 90,3,'p'
/*借助臨時表*/
select identity(int,1,1)as id,checksum(*)as t,* into #b from #a
/*利用臨時表排序*/
select a,b,c from #b where id=(select id-1 from #b where t=(select checksum(*) from #a where c='x'))
/*******************有順序可以比較大小**********************/
create table  #a (a int,b int,c char(1))
insert into #a select 3,3,'a'
insert into #a select 2,3,'b'
insert into #a select 1,3,'c'
insert into #a select 5,3,'d'
insert into #a select 4,3,'e'
insert into #a select 6,3,'o'
insert into #a select 7,3,'x'
insert into #a select 100,3,'z'
insert into #a select 90,3,'p'
select top 1 * from #a where a<(select a from #a where c='x') order by a desc