有续排序与无续排序.txt

SQL语言常用的一些命令各代码

/*************无续取某行上一条记录****************/
/*测试表*/
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