I have a query, and I need to find the row number that the query return the answer. I do not have a counter field. How do I do it?
我有一个查询,我需要找到查询返回答案的行号。我没有专柜。我该怎么做?
Thanks in advance.
提前致谢。
4 个解决方案
#1
SELECT
ROW_NUMBER() OVER (<your_id_field_here> ORDER BY <field_here>) as RowNum,
<the_rest_of_your_fields_here>
FROM
<my_table>
#2
If you have a primary key, you can use this method on SQL Server 2005 and up:
如果您有主键,则可以在SQL Server 2005及更高版本上使用此方法:
SELECT
ROW_NUMBER() OVER (ORDER BY PrimaryKeyField) as RowNumber,
Field1,
Field2
FROM
YourSourceTable
If you don't have a primary key, what you may have to do is duplicate your table into a mermory table (or a temp table if it is very large) using a method like this:
如果您没有主键,您可能需要做的是使用如下方法将表复制到mermory表(或临时表(如果它非常大)):
DECLARE @NewTable table (
RowNumber BIGINT IDENTITY(1,1) PRIMARY KEY,
Field1 varchar(50),
Field2 varchar(50),
)
INSERT INTO @NewTable
(Field1, Field2)
SELECT
Field1,
Field2
FROM
YourSourceTable
SELECT
RowNumber,
Field1,
Field2
FROM
@NewTable
The nice part about this, is that you will be able to detect identical rows if your source table does not have a primary key.
关于这一点的好处是,如果源表没有主键,您将能够检测到相同的行。
Also, at this point, I would suggest adding a primary key to every table you have, if they don't already have one.
此外,在这一点上,我建议为每个表添加一个主键,如果它们还没有。
#3
create table t1 (N1 int ,N2 int)
创建表t1(N1 int,N2 int)
insert into t1 select 200,300
插入t1选择200,300
insert into t1 select 200,300
插入t1选择200,300
insert into t1 select 300,400
插入t1选择300,400
insert into t1 select 400,400 ..... ......
插入t1选择400,400 ..... ......
select row_number() over (order by [N1]) RowNumber,* from t1
选择row_number()over(从[N1]开始)RowNumber,*来自t1
#4
I have a Query, and I need to find the row number that the query return the answer. I do not have any counter field. how I do it ?
我有一个查询,我需要找到查询返回答案的行号。我没有任何反制领域。我怎么做的?
If I understand your question correctly, you have some sort of query that returns a row, or a few rows (answer, answers) and you want to have a row number associates with an "answer"
如果我正确地理解了你的问题,你会得到一些返回行或几行(答案,答案)的查询,并且你想要一个与“答案”相关联的行号
As you said you do not have any counter field so what you need to to is the following:
如你所说,你没有任何计数器字段,所以你需要的是以下内容:
- decide on the ordering criteria
- add the counter
- run the query
决定订购标准
加上柜台
运行查询
It would really help if you provided more details to your question(s). If you confirm that I understand your question correctly, I will add a code example
如果您为问题提供了更多详细信息,那将会非常有帮助。如果您确认我正确理解了您的问题,我将添加一个代码示例
#1
SELECT
ROW_NUMBER() OVER (<your_id_field_here> ORDER BY <field_here>) as RowNum,
<the_rest_of_your_fields_here>
FROM
<my_table>
#2
If you have a primary key, you can use this method on SQL Server 2005 and up:
如果您有主键,则可以在SQL Server 2005及更高版本上使用此方法:
SELECT
ROW_NUMBER() OVER (ORDER BY PrimaryKeyField) as RowNumber,
Field1,
Field2
FROM
YourSourceTable
If you don't have a primary key, what you may have to do is duplicate your table into a mermory table (or a temp table if it is very large) using a method like this:
如果您没有主键,您可能需要做的是使用如下方法将表复制到mermory表(或临时表(如果它非常大)):
DECLARE @NewTable table (
RowNumber BIGINT IDENTITY(1,1) PRIMARY KEY,
Field1 varchar(50),
Field2 varchar(50),
)
INSERT INTO @NewTable
(Field1, Field2)
SELECT
Field1,
Field2
FROM
YourSourceTable
SELECT
RowNumber,
Field1,
Field2
FROM
@NewTable
The nice part about this, is that you will be able to detect identical rows if your source table does not have a primary key.
关于这一点的好处是,如果源表没有主键,您将能够检测到相同的行。
Also, at this point, I would suggest adding a primary key to every table you have, if they don't already have one.
此外,在这一点上,我建议为每个表添加一个主键,如果它们还没有。
#3
create table t1 (N1 int ,N2 int)
创建表t1(N1 int,N2 int)
insert into t1 select 200,300
插入t1选择200,300
insert into t1 select 200,300
插入t1选择200,300
insert into t1 select 300,400
插入t1选择300,400
insert into t1 select 400,400 ..... ......
插入t1选择400,400 ..... ......
select row_number() over (order by [N1]) RowNumber,* from t1
选择row_number()over(从[N1]开始)RowNumber,*来自t1
#4
I have a Query, and I need to find the row number that the query return the answer. I do not have any counter field. how I do it ?
我有一个查询,我需要找到查询返回答案的行号。我没有任何反制领域。我怎么做的?
If I understand your question correctly, you have some sort of query that returns a row, or a few rows (answer, answers) and you want to have a row number associates with an "answer"
如果我正确地理解了你的问题,你会得到一些返回行或几行(答案,答案)的查询,并且你想要一个与“答案”相关联的行号
As you said you do not have any counter field so what you need to to is the following:
如你所说,你没有任何计数器字段,所以你需要的是以下内容:
- decide on the ordering criteria
- add the counter
- run the query
决定订购标准
加上柜台
运行查询
It would really help if you provided more details to your question(s). If you confirm that I understand your question correctly, I will add a code example
如果您为问题提供了更多详细信息,那将会非常有帮助。如果您确认我正确理解了您的问题,我将添加一个代码示例