需要在 sqlserver 2005 中使用 ROW_NUMBER 而不超过 [英] need to use ROW_NUMBER without over in sqlserver 2005

问题描述

可能的重复:
SQL 中没有 over 的 ROW_NUMBER()

我有一个对每列进行排序的网格，并且一次只能显示 50 行.

I have a grid with sorting on each column and have to show only 50 rows at a time.

我使用 sql server 2005 作为数据库.

I am using sql server 2005 as database.

现在sql server 2005 中有ROW_NUMBER 函数可以用行号过滤数据，但是Over(order by [Column]) 是必须使用的.在我的情况下，我必须用不同的列对网格进行排序，所以我不能在 order by 子句中使用静态列名称.限制是，我必须使用静态查询.

Now there is ROW_NUMBER function in sql server 2005 to filter the data with row number but Over(order by [Column]) is mandatory to use. In my case I have to sort my grid with different columns so I can not use the static columns name in order by clause. The restriction is , I have to use static query.

你们能帮我解决这个问题吗?

Can you guys help me out on this problem?

推荐答案

每列使用多个 ROW_NUMBER 子句并选择相关的一个:

Use several ROW_NUMBER clauses per column and choose the relevant one:

....
    ROW_NUMBER() OVER (ORDER BY [Column1]) as rn1,
    ROW_NUMBER() OVER (ORDER BY [Column2]) as rn2,
    ROW_NUMBER() OVER (ORDER BY [Column3]) as rn3,
    ROW_NUMBER() OVER (ORDER BY [Column4]) as rn4
....

OR 使用 CASE 表达式.注意:所有数据类型必须兼容

OR use a CASE expression. Note: all datatypes must be compatible

ROW_NUMBER() OVER (ORDER BY CASE @sort
                     WHEN 1 THEN [Column1]
                     WHEN 1 THEN [Column2]
                     WHEN 1 THEN [Column3]
                     ...
                   END

OR 如果您确实想要任意行号，请执行以下操作:

OR If you really want an arbitrary row number then do this:

ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rn

这篇关于需要在 sqlserver 2005 中使用 ROW_NUMBER 而不超过的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！