SQL 在sql中自动生成序列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16750638/
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
Auto generate serial numbers in sql
提问by Shafiq Ahmed
table orders
+----+--------+------------+
|s.no| id | ordername |
+----+--------+------------+
| 1 | 34 | name |
+----+--------+------------+
| 2 | 35 | name |
+----+--------+------------+
| 3 | 36 | name |
+----+--------+------------+
| 4 | 37 | name |
+----+--------+------------+
| 5 | 38 | name |
+----+--------+------------+
write query for auto generate serial numbers and when i delete an order from sql so serial numbers should be as 1234567 in serial_number row and i used order id as auto increment
编写自动生成序列号的查询，当我从 sql 中删除订单时，序列号在 serial_number 行中应为 1234567，我使用订单 ID 作为自动增量
回答by Gordon Linoff
You can write the query in most dialects of sql as:
您可以用大多数 sql 方言将查询编写为：
select row_number() over (order by id) as "s.no",
id, ordername
from orders o
Here is syntax that works in SQL Server:
以下是适用于 SQL Server 的语法：
with toupdate as (
select row_number() over (order by id) as newval,
o.*
from orders o
)
update toupdate set [s.no] = newval;
回答by Amit Kabadi
FOR MYSQL USERSBased on your reasons for not wanting to use user defined variables as wanting to avoid having 2 queries, one for inializing and one to use it you could use the following:
FOR MYSQL 用户基于您不想使用用户定义变量的原因，因为您想避免有 2 个查询，一个用于初始化，另一个用于使用它，您可以使用以下内容：
SELECT @a:=@a+1 serial_number,
marks
FROM student_marks,
(SELECT @a:= 0) AS a;
回答by DEVNAG BHARAD
select @a:=0;
SELECT @a:=@a+1 serial_number,
marks
FROM student_marks,
It works for me hope i will for for you too.
它对我有用，希望我也对你有用。
