为第"th"个最高分创建存储过程 [英] creating stored procedure for 'th' highest marks

问题描述

我有一张桌子

I''ve a table

create table marks(stdName nvarchar(30), marks float)

insert into marks
select 'std1', 98
union all select 'std2', 96
union all select 'std3', 95
union all select 'std4', 97
union all select 'std5', 93

我想创建存储过程以选择行号为'th'的行作为给定的输入参数.假设输入参数为3，则应显示"std2"， 96或如果输入参数为5，则应显示"std5"，即93.

I want to create stored procedure to select the row with ''th'' highest marks as given input parameter.suppose if the input parameter is 3, it should display ''std2'', 96 or if input parameter is 5, it should display ''std5'', 93.

推荐答案

程序本身非常简单，修改语句根据要求，然后将其包含在过程中，请参见创建过程 [

The procedure itself is quite straightforward, modify the statement as per requirements and then include inside a procedure, see CREATE PROCEDURE [^]

About the query, I would advice to separate the first column to two separate columns if the data describes different things. Concatenated columns should never be stored inside the database.

If the columns would be separate, then your query to fetch the row could be something like:

INSERT INTO Marks (strName, anotherfield, marks)
SELECT stdName, anotherField, marks
FROM  (
             select 'std' as stdName, 1 as AnotherField, 98 AS Marks
   union all select 'std' as stdName, 2 as AnotherField, 96 AS Marks
   union all select 'std' as stdName, 3 as AnotherField, 95 AS Marks
   union all select 'std' as stdName, 4 as AnotherField, 97 AS Marks
   union all select 'std' as stdName, 5 as AnotherField, 93 AS Marks) AS sub
WHERE sub.AnotherField = @procedureParameter

如果要求从Table 中检索第n 行，则Sql Server的ROW_NUMBER 函数可用于此目的，如下所示

If the requirement is to retrieve nth row from the Table then, the ROW_NUMBER function of Sql Server can be used for this purpose as shown below

CREATE PROCEDURE GetnthRow
    @nthRow nvarchar(30)
AS
WITH OrderedMarks AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY stdName) as RowNo, stdName, marks
    FROM marks
)
SELECT stdName, marks
FROM OrderedMarks
WHERE RowNo=@nthRow
GO

这篇关于为第"th"个最高分创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！