SQL 如何使用sql查询生成序列号并用它更新表？

How to generate serial number using sql query and update the table with it?

提问by Arbaaz

I have a sql table which looks like :

我有一个 sql 表，它看起来像：

srno | passportnumber|flightnumber
Null | ABC123        |AI-2000
Null | ab3333        |AI-2011
Null | ab565235      |AI-2562
Null | ABC123        |AI-2025
Null | ABC123        |AI-5623
Null | XYZ12334      |AI-5625

I need a query to sort them by passportnumber so that repetitive passportnumber rpws are next to eachother and then i want to update the table to insert the serialnumber. the result should be something like this :

我需要一个查询来按护照号码对它们进行排序，以便重复的护照号码 rpws 彼此相邻，然后我想更新表以插入序列号。结果应该是这样的：

1    | ABC123        |AI-2000
2    | ABC123        |AI-2025
3    | ABC123        |AI-5623
4    | ab3333        |AI-2011
5    | ab565235      |AI-2562
6    | XYZ12334      |AI-5625

回答by Mahmoud Gamal

Try this:

尝试这个：

;WITH Ranked
AS
(
   SELECT *, ROW_NUMBER() OVER(ORDER BY passportnumber) row_num
   FROM Tablename
) 
UPDATE Ranked
SET srno = row_num;

Live Demo

现场演示