在sql查询中使用rollno交换序列号 [英] swaping serial no with rollno in sql query

问题描述

你好，早上好了





我有一个问题，就是把卷号换成序号。

我有一张桌子像





id roll_No name

--------- -------------------------

1 1 raj

2 3 jogindar
3 6 yograj

4 7 amit

5 8 Upkar

6 10 hemraj

8 21 khemraj





我希望得到这样的结果

======== ===============================================



id roll_No aname

-------------------------- ---------

1 amit

4 2 amit

6 3 hemraj

2 4 jogindar

8 5 khemraj

5 6 Upkar

3 7 yograj



= - ==================== =========

UPDATE客户SET名称=地址，地址=姓名

^

此查询工作正常但是



更新tbl_testing set roll =（选择row_number（）结束（按照aname命令））



this查询不可用













请帮助我紧急



提前感谢

hello all good moring


I have a problem to swaping roll no with serial No.
I have a Table like


id roll_No name
----------------------------------
1 1 raj
2 3 jogindar
3 6 yograj
4 7 amit
5 8 Upkar
6 10 hemraj
8 21 khemraj


and I want the result of like this
=======================================================

id roll_No aname
-----------------------------------
1 1 amit
4 2 amit
6 3 hemraj
2 4 jogindar
8 5 khemraj
5 6 Upkar
3 7 yograj

=-=============================
UPDATE Customer SET Name=Address , Address=Name
^
this query is working properly but

update tbl_testing set roll=(select row_number() over (order by aname))

this query is not usable






please help me its urgent

thanks in advance

推荐答案

尝试这个....

- 与OriginalGriff解决方案相同但你必须添加PARTITION BY

Try this....
-- Same as OriginalGriff Solution but u have to add "PARTITION BY"

UPDATE MyTable SET RollNo=j.RowNo
FROM MyTable INNER JOIN
   (SELECT Id, ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name]) AS RowNo FROM MyTable) as j
   ON MyTable.Id = j.Id

ROW_NUMBER（Transact-SQL） [ ^ ]

如何使用ROW_NUMBER（）枚举和分区SQL Server中的记录 [ ^ ]



Rega rds，

GVPrabu

ROW_NUMBER (Transact-SQL)[^]
How to use ROW_NUMBER() to enumerate and partition records in SQL Server[^]

Regards,
GVPrabu

这有点复杂，但它是可行的：

This is a little complicated, but it's doable:

UPDATE MyTable SET RollNo=j.RowNo
FROM MyTable INNER JOIN
   (SELECT Id, ROW_NUMBER() OVER (ORDER BY [Name]) AS RowNo FROM MyTable) as j
   ON MyTable.Id = j.Id

你

When you

SELECT * FROM MyTable

之后，你将部分得到你想要的东西：行不会被订购，但Roll_no将是一个你需要的。

要按照你想要的确切顺序返回它们，你需要指定一个排序顺序 - 因为如果不这样做，SQL可以按任何顺序返回行：

after that, you will partly get what you want: The rows will not be ordered, but the Roll_no will be as you wanted.
To return them in the exact order you want, you need to specify a sort order - because SQL can return rows in any order convenient to it if you don't:

SELECT * FROM MyTable ORDER BY RollNo

这篇关于在sql查询中使用rollno交换序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！