在orders表里有两个字段,username是名字字段,sequence是用来排名的字段未排序的结果是
username sequence
aaa 44
bbb 21
ccc 52
ddd 17
fff 11
hh 35
gg 93
此时aaa是第一名,bbb是第二名,ccc是第三名.当以select * from orders order by sequence语句排序得结果是
fff 11
ddd 17
bbb 21
hh 35
aaa 44
ccc 52
gg 93怎样知道fff就是第一名,ddd就是第二名,bbb就是第三名?
username sequence
aaa 44
bbb 21
ccc 52
ddd 17
fff 11
hh 35
gg 93
此时aaa是第一名,bbb是第二名,ccc是第三名.当以select * from orders order by sequence语句排序得结果是
fff 11
ddd 17
bbb 21
hh 35
aaa 44
ccc 52
gg 93怎样知道fff就是第一名,ddd就是第二名,bbb就是第三名?
fff 11
ddd 17
bbb 21
hh 35
aaa 44
ccc 52
gg 93
如果我要获取排序后结果中的第5名(就是aaa这个名字)应该如何获得。sql语句怎样实现?
(select top 5 * from orders order by sequence ) ls
(col1 varchar(10),col2 int)
insert a select 'aaa',44
union all select 'bbb',21
union all select 'ccc',52
union all select 'ddd',17
union all select 'fff',11
union all select 'hh',35
union all select 'gg',93select *,(select count(1) from a where col2<=t.col2) as x from a t order by x
(col1 varchar(10),col2 int)
insert a select 'aaa',44
union all select 'bbb',21
union all select 'ccc',52
union all select 'ddd',17
union all select 'fff',11
union all select 'hh',35
union all select 'gg',93declare @t table(id int identity(1,1),col1 varchar(10),col2 int)
insert into @t select * from a
select * from @t where id=5--这里写你要的位置就可以了
drop table a
当以select * from orders order by sequence语句排序得结果是
fff 11
ddd 17
bbb 21
hh 35
aaa 44
ccc 52
gg 93
如果要查询是ccc是第几名又如何实现?
(col1 varchar(10),col2 int)
insert a select 'aaa',44
union all select 'bbb',21
union all select 'ccc',52
union all select 'ddd',17
union all select 'fff',11
union all select 'hh',35
union all select 'gg',93select * from (select *,(select count(1) from a where col2<=t.col2) as x from a t)p where col1='ccc'
(
CON INT IDENTITY (1, 1),
username VARCHAR(100),
seq INT
PRIMARY KEY(CON))
INSERT INTO #TEST1(username,seq)
SELECT [username],[sequence] FROM orders ORDER BY sequence
SELECT * FROM #TEST1
DROP TABLE #TEST1