求一条sql语句

2009-08-24 10:28:55 124 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ROS2_Moveit2_Ur5e_Grasp项目详解（二）：核心模块功能解析本文深入解析了ROS2_Moveit2_Ur5e_Grasp项目的核心模块功能。重点阐述了OctoMap建图模块如何为机械臂提供实时三维环境感知，并详细介绍了Robotiq夹爪与MoveIt2的集成控制、UR5e的运动规划策略，以及视觉模块如何协同工作，最终实现从目标检测到动态抓取的完整闭环。阅读详情

求：存储过程

成绩表
id name score(成绩）

我想得到某一个用户的成绩排名数

根据id得到成绩的排名是多少？

...全文

JDK8升级JDK17过程中遇到的那些坑 JDK8虽然非常好，但是JDK版本已经发布到JDK20了，且JDK8后的版本升级了很多新的特性，如模块化、ZGC以及虚拟线程、结构性并发等，也是非常有吸引力的，所以决定将基于JDK8的项目升级到最近的LTS版本JDK17。阅读详情

claymore1114

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

claymore1114 2009-08-24

打赏
举报

结贴结贴

水族杰纶 2009-08-24

打赏
举报

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([id] int,[score] int,[name] nvarchar(4))

insert [TB]

select 1,100,N'张三' union all

select 2,80,N'李四' union all

select 3,80,N'王五' union all

select 4,75,N'杨六' union all

select 5,75,N'方七'



select id=(select count(distinct [score])+1 from TB where T.[score]<[score]),

       [score],

       [name]

from TB t

/*

id          point       name

----------- ----------- ----

1           100         张三

2           80          李四

2           80          王五

3           75          杨六

3           75          方七



(5 個資料列受到影響)



*/

黄_瓜 2009-08-24

打赏
举报

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([id] int,[point] int,[name] varchar(4))

insert [TB]

select 1,100,'张三' union all

select 2,80,'李四' union all

select 3,80,'王五' union all

select 4,75,'杨六' union all

select 5,75,'方七'



select id=(select count([point])+1 from TB where T.[point]<[point]),point,name

from TB t

--where ........



/*

id          point       name

----------- ----------- ----

1           100         张三

2           80          李四

2           80          王五

4           75          杨六

4           75          方七



(5 行受影响)





*/

黄_瓜 2009-08-24

打赏
举报

[Quote=引用 2 楼 fredrickhu 的回复:]
SQL code--> 测试数据：[TB]ifobject_id('[TB]')isnotnulldroptable[TB]createtable[TB]([id]int,[point]int,[name]varchar(4))insert[TB]select1,100,'张三'unionallselect2,80,'李四'unionallselect3,80,'王五'unionallselect¡
[/Quote]
原来是想要这种结果呀。xiaoF可以考虑当个侦探了

gw6328 2009-08-24

打赏
举报

都比较爽.

gw6328 2009-08-24

打赏
举报

select rn from (select rn=row_number() over(order by score desc),* from tb) where id=@id
学习!

SQL77 2009-08-24

打赏
举报

[code=SQL]

CREATE PROC P(@ID INT)

AS 

BEGIN

SELECT ID,(SELECT COUNT(*) FROM TB WHERE score>=T.score)AS NUM INTO #T FROM TB T

SELECT NUM FROM #T WHERE ID=@ID

END

[/code]

ChinaJiaBing 2009-08-24

打赏
举报



select id, score,row_number()over(order by score desc) as 名次 from tb

feixianxxx 2009-08-24

打赏
举报

if object_id('成绩表') is not null 

drop table 成绩表

go

create table 成绩表([id] int ,score int,[name] varchar(4))

insert 成绩表

select 1,100,'张三' union all

select 2,90,'李四' union all

select 3,80,'王五' union all

select 4,85,'杨六' union all

select 5,75,'方七'

go

create proc  ck1

@id int 

as

begin 

declare @paim int

select @paim=rn 

from (select rn=ROW_NUMBER() over(order by score  desc ),* from 成绩表) k

where Id=@id 

select @paim

end

exec ck1  4

/*

-----------

3



*/

--小F-- 2009-08-24

打赏
举报

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

go

create table [TB]([id] int,[point] int,[name] varchar(4))

insert [TB]

select 1,100,'张三' union all

select 2,80,'李四' union all

select 3,80,'王五' union all

select 4,75,'杨六' union all

select 5,75,'方七'



select id=(select count([point])+1 from TB where T.[point]<[point]),point,name

from TB t

where name='张三'



/*id          point       name

----------- ----------- ----

1           100         张三



(1 行受影响)*/

ChinaJiaBing 2009-08-24

打赏
举报



select id, score,row_number()over(order by score desc) as 名次 from tb where name='性名'

Yang_ 2009-08-24

打赏
举报

select *,rank() over(order by score desc)

from 成绩表

黄_瓜 2009-08-24

打赏
举报

要不就给出数据和你想要的结果吧

--小F-- 2009-08-24

打赏
举报

2楼的例子加上查询条件就可以了

liangCK 2009-08-24

打赏
举报

DECLARE @id int;

SET @id = 10;



SELECT *

FROM (

    SELECT id,name,score,

       rnk = (SELECT COUNT(*)+1 FROM tb WHERE A.score > score)

    FROM tb AS A

) AS T

WHERE id = @id

--2005直接用dense_rank()

csdyyr 2009-08-24

打赏
举报

create proc test

@id int

as

  select seq

  from (

  select *,seq=dense_rank() over (order by score desc)

  ) t

  where id=@id

feixianxxx 2009-08-24

打赏
举报

create proc  c

@id int 

as

begin 

declare @paim int

select @paim=rn 

from (select rn=ROW_NUMBER() over(order by score  desc ),* from 成绩表) k

where Id=@id 

select @paim

end

--小F-- 2009-08-24

打赏
举报

--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([id] int,[point] int,[name] varchar(4))

insert [TB]

select 1,100,'张三' union all

select 2,80,'李四' union all

select 3,80,'王五' union all

select 4,75,'杨六' union all

select 5,75,'方七'



select id=(select count([point])+1 from TB where T.[point]<[point]),point,name

from TB t



/*

id          point       name

----------- ----------- ----

1           100         张三

2           80          李四

2           80          王五

4           75          杨六

4           75          方七



(5 行受影响)





*/



drop table TB



--> 测试数据：[TB]

if object_id('[TB]') is not null drop table [TB]

create table [TB]([id] int,[point] int,[name] Nvarchar(4))

insert [TB]

select 1,100,N'张三' union all

select 2,80,N'李四' union all

select 3,80,N'王五' union all

select 4,75,N'杨六' union all

select 5,75,N'方七'



select id=(select count(distinct point) from TB where T.[point]<=[point]),

       point,

       [name]

from TB t

/*

id          point       name

----------- ----------- ----

1           100         张三

2           80          李四

2           80          王五

3           75          杨六

3           75          方七



(5 個資料列受到影響)

*/

黄_瓜 2009-08-24

打赏
举报

select id, score from tb where name='张三'

求一条sql语句

相关推荐