开销应该在这句:order by id desc,而且我怎么没看出你的语句可以实现找上下5名?还有把语句尽可能贴全来看看。另外: set @a = 0; select b.rank as 排名,a.* from ranking a join(select @a:=@a+1 as rank,id from ranking order by id desc) as b on a.id = b.id 红字部分,你的是什么数据库?
select id,uid,msg,time from message order by id desc limit $rank-5,10 上下5名的话,我是再通过查出来的那个人的排名,然后减5,取10条
mysql?你这样累加肯定慢拉。何不一次搞出来。
我尽量写写,但是不知道SQLServer能不能完全移植到mysql
不知道是不是你想要的---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-01-16 15:25:58 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([id] int,[uid] int,[integral] int) insert [huang] select 1,8,10 union all select 2,8,10 union all select 3,8,10 union all select 4,8,10 union all select 6,8,10 union all select 7,8,10 union all select 8,8,10 union all select 9,8,10 union all select 10,8,10 union all select 11,8,10 union all select 12,8,10 --------------开始查询-------------------------- DECLARE @a INT SET @a=8 SELECT * FROM huang b WHERE id BETWEEN @a - 5 AND @a + 5----------------结果---------------------------- /* id uid integral ----------- ----------- ----------- 3 8 10 4 8 10 6 8 10 7 8 10 8 8 10 9 8 10 10 8 10 11 8 10 12 8 10(9 行受影响)*/
就要我的排名,名次就是按@a:=@a:=@a+1这样来的,排名目前是按ID排,因为插数据方便额..
SELECT id,RANK()OVER(ORDER BY id DESC )rankid FROM huang bsqlserver有这个排名函数,自动排名,但是不知道mysql有没有。有的话你就不用这样累加了。/* id rankid ----------- -------------------- 12 1 11 2 10 3 9 4 8 5 7 6 6 7 4 8 3 9 2 10 1 11(11 行受影响) */
set @a = 0;
select b.rank as 排名,a.* from ranking a
join(select @a:=@a+1 as rank,id from ranking order by id desc) as b on a.id = b.id
红字部分,你的是什么数据库?
上下5名的话,我是再通过查出来的那个人的排名,然后减5,取10条
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-16 15:25:58
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[uid] int,[integral] int)
insert [huang]
select 1,8,10 union all
select 2,8,10 union all
select 3,8,10 union all
select 4,8,10 union all
select 6,8,10 union all
select 7,8,10 union all
select 8,8,10 union all
select 9,8,10 union all
select 10,8,10 union all
select 11,8,10 union all
select 12,8,10
--------------开始查询--------------------------
DECLARE @a INT
SET @a=8
SELECT *
FROM huang b
WHERE id BETWEEN @a - 5 AND @a + 5----------------结果----------------------------
/*
id uid integral
----------- ----------- -----------
3 8 10
4 8 10
6 8 10
7 8 10
8 8 10
9 8 10
10 8 10
11 8 10
12 8 10(9 行受影响)*/
FROM huang bsqlserver有这个排名函数,自动排名,但是不知道mysql有没有。有的话你就不用这样累加了。/*
id rankid
----------- --------------------
12 1
11 2
10 3
9 4
8 5
7 6
6 7
4 8
3 9
2 10
1 11(11 行受影响)
*/