如何在select语句中选择行号 [英] how to select row number in select statement

问题描述

您好，

我有一个表格测试，其中总共有1000条记录



当我选择一些我想要的字段时返回135行

Hello ,
I have a table test in which there are total 1000 record

when i select some fields which i want then it return 135 rows

select distinct 
column1,column2,column3,column4,column5 from test

现在我想添加一个代表行号的列，如1 2 3 ..... 135

now i want to add a column which represent row number like 1 2 3 ..... 135

推荐答案

最简单方法是使用 TOP 指令。

The simplest way is to use TOP instruction.

SELECT TOP (135) <Field_list>
FROM TableName

请参阅： TOP（Transact-SQL） [ ^ ]



如果你想添加行编号，使用 ROW_NUMBER（） [ ^ ]函数。

Please, see: TOP (Transact-SQL)[^]

If you would like to add row number, use ROW_NUMBER()[^] function.

SELECT TOP (135) ROW_NUMBER() OVER(ORDER BY <FieldName>) AS RowNo, <Field_list>
FROM TableName

您可以设置行数限制通过使用 ROW_NUMBER（）函数。

You can set the limit of rows via using ROW_NUMBER() function.

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY <FieldName>) AS RowNo, <Field_list>
    FROM TableName)
AS T 
WHERE RowNo<=135

以同样的方式，您可以从定义的范围中获取数据，例如：

In the same way, you can fetch data from defined range, for example:

DECLARE @rFrom INT = 136
DECLARE @rTo INT = 170
SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY <FieldName>) AS RowNo, <Field_list>
    FROM TableName)
AS T 
WHERE RowNo BETWEEN @rFrom AND @rTo

我称之为''custom pagination';）

I call it: 'custom pagination' ;)

如果你还需要排序，请试试这个

if u need also row no,try this

select top 135 column1,ROW_NUMBER() over(order by column1 asc) from table

我通常觉得它更容易在我定义表时添加整数自动增量列（有时它也是主要的k ey） - 那样



a）当我插入一个新行时，我有一个独特的值

b）我可以将该值用作关键字如果我需要



你可以做一个sql修改表来添加列（我想，取决于'哪个sql风味/变种'你是使用） - 然后你需要一个小的游标更新程序来更新表并逐步设置值 - 它是一个PITA - 你必须为所有添加的新行等维护它..更容易为你自动增加值在插入imho



作为解决方案4指出，row_number（）应该做你想做的事情



'g'

I usually find it easier to add an integer auto increment column when I define the table (sometimes its also the primary key) - that way

a) when I insert a new row I have a unique value
b) I can use the value as a key if I need to

you can do a sql modify table to add the column (I think, depending on 'which sql flavour/variant' you're using) - then you'd need a small cursor update program to update the table and set the value incrementally - its a PITA - you then have to maintain it for all new rows added etc .. easier to have the value auto incremented for you on an insert imho

as solution 4 points out, row_number() should do what you want

'g'

这篇关于如何在select语句中选择行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！