从表中选择记录数 [英] Select number of records from table

问题描述

我通过使用从商品中选择top(5)itemCode，ItemName，Price"从表中获取记录.
现在，我不想再记录5条记录.如何创建对该查询的查询.
之后，我也不想再记录前5条记录.

I get records from the table by using "Select top(5) itemCode,ItemName,Price from Item"
Now I wan''t to take next 5 records. How can I create query to that one.
After that I wan''t to take the previous 5 records also.

推荐答案

这是一个将为您完成的存储过程，只需在服务器上对其进行编译并使用1或0作为参数即可运行它.

您将看到是否使用1作为参数，它将返回前5个，否则返回0，下一个5

Hi,

Here is a stored procedure that will do it for you, just compile it on your server and run it using either 1 or 0 as a parameter.

You will see if you use 1 as a parameter it will return top 5, otherwise 0 will return the next 5

<br />
execute SP_GetTop5CP 1<br />
execute SP_GetTop5CP 0<br />
execute SP_GetTop5CP 1<br />

if (object_id('SP_GetTop5CP') is not null)
begin
    drop proc SP_GetTop5CP
end
go
/*
This stored procedure will return the top 5 if called with 1 parameter
and will return the 6-10 if called with 0 parameter
*/
create proc SP_GetTop5CP @First5 bit = 1
as
begin
    declare @i int
    select top(10) itemCode,ItemName,Price into #1 from Item
    alter table #1 add IDX int
    set @i = 0
    update #1 set IDX = @i, @i = @i + 1
    if (@First5 = 1)
    begin
        --you want to get the first 5
        delete from #1 where IDX > 5
    end
    else
    begin
        --you want to get the next 5
        delete from #1 where IDX <= 5
    end
    alter table #1 drop column IDX
    select * from #1
end
go
grant exec on SP_GetTop5CP to public
go

希望这会有所帮助

问候

Terence

Hope this helps

Regards

Terence

您的数据库架构将必须支持这样做.您不能只对任何桌子都这样做.阅读 ^ ].

Your database schema is going to have to support doing this. You can''t do it for just any table. Read these[^].

这是一个存储过程，将在第11至15日或第11至13日(如果有所不同)的情况下为您完成操作

Hi,
Here is a stored procedure that will do it for you in case of 11th to 15th or 11th to 13th (if it varies)

<pre lang="sql">create proc SP_GetTopNCP<br />
    @RecSize    INT,<br />
    @PageNo     INT<br />
as<br />
begin<br />
    select ROW_NUMBER() OVER(ORDER BY itemCode ASC) as [rowId],itemCode,ItemName,Price into #1 from Item<br />
    select top(RecSize) * from #1 WHERE rowID> (@RecSize * (@PageNo-1))<br />
end</pre><br />

这篇关于从表中选择记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！