用于在查询结果中输出行号的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1293390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL to output line number in results of a query
提问by madewulf
I would like to generate a line number for each line in the results of a sql query. How is it possible to do that?
我想为 sql 查询结果中的每一行生成一个行号。怎么可能做到这一点？
Example: In the request
示例：在请求中
select distinct client_name from deliveries
I would like to add a column showing the line number
我想添加一列显示行号
I am working on sql server 2005.
我在 sql server 2005 上工作。
回答by Roee Adler
It depends on the database you are using. One option that works for SQL Server, Oracle and MySQL:
这取决于您使用的数据库。一种适用于 SQL Server、Oracle 和 MySQL 的选项：
SELECT ROW_NUMBER() OVER (ORDER BY SomeField) AS Row, *
FROM SomeTable
Change SomeField and SomeTable is according to your specific table and relevant field to order by. It is preferred that SomeField is unique in the context of the query, naturally.
更改 SomeField 和 SomeTable 是根据您的特定表和相关字段进行排序。自然地，最好 SomeField 在查询的上下文中是唯一的。
In your case the query would be as follows (Faiz crafted such a query first):
在您的情况下，查询如下（Faiz 首先制作了这样的查询）：
SELECT ROW_NUMBER() OVER (ORDER BY client_name) AS row_number, client_name
FROM (SELECT DISTINCT client_name FROM deliveries) TempTable
I think it won't work for SQLite (if someone can correct me here I would be grateful), I'm not sure what's the alternative there.
我认为它不适用于 SQLite（如果有人能在这里纠正我，我将不胜感激），我不确定那里有什么替代方案。
回答by Faiz
You can use the ROW_NUMBER function for this. Check the syntax for this here http://msdn.microsoft.com/en-us/library/ms186734.aspx
您可以为此使用 ROW_NUMBER 函数。在此处检查语法http://msdn.microsoft.com/en-us/library/ms186734.aspx
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY FirstName) AS 'Row#'
FROM Sales.vSalesPerson;
For your query,
对于您的查询，
SELECT client_name, ROW_NUMBER() Over (Order By client_name) AS row_number
FROM (select distinct client_name from deliveries) SQ
will work.
将工作。
回答by APC
In Oracle
在甲骨文
SQL> select row_number() over (order by deptno) as rn
2 , deptno
3 from
4 ( select distinct deptno
5 from emp
6 )
7 /
RN DEPTNO
---------- ----------
1 10
2 20
3 30
SQL>
回答by user7912960
In SQL we are also using this query:
在 SQL 中，我们也使用这个查询：
select row_number() over (order by table_kid) as S_No ,table_columnname,table_columnname2 from tablename where table_fieldcondition='condtionnal falg or data ';
Here table_kid or may be other table_columnname
这里 table_kid 或可能是其他 table_columnname
回答by giogio ben
OR you could also do
或者你也可以这样做
SELECT DISTINCT client_name, @num := @num + 1 AS lineNum(this is your new col)
FROM deliveries
JOIN (SELECT @num :=0) AS n ON 1=1;
hope this helps too :)
希望这也有帮助:)
