27,579
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[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 個資料列受到影響)
*/
--> 测试数据:[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 行受影响)
*/
[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]
select id, score,row_number()over(order by score desc) as 名次 from tb
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
*/
--> 测试数据:[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 行受影响)*/
select id, score,row_number()over(order by score desc) as 名次 from tb where name='性名'
select *,rank() over(order by score desc)
from 成绩表
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
create proc test
@id int
as
select seq
from (
select *,seq=dense_rank() over (order by score desc)
) t
where id=@id
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
--> 测试数据:[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 個資料列受到影響)
*/
select id, score from tb where name='张三'