SQL 如何对列中的值进行排序并更新表？

How to sort values in columns and update table?

提问by Sabre

I'm completely new to sql and can't do that myself. So I need your help. I want to sort values in column and then save changes. But I don't know how to do that.

我对 sql 完全陌生，我自己也做不到。所以我需要你的帮助。我想对列中的值进行排序，然后保存更改。但我不知道该怎么做。

Table looks like that:

表看起来像这样：

Id | Name | SomeDescription
---------------------------
1  |Best  | Description1
2  |Worth | Description2
3  |Good  | Description3

I want to get something like that:

我想得到这样的东西：

Id | Name | SomeDescription
---------------------------
1  |Best  | Description1
2  |Good  | Description3
3  |Worth | Description2

So I need to sort "id" and "name" columns.

所以我需要对“id”和“name”列进行排序。

I use following statement to sort values of "name" column:

我使用以下语句对“名称”列的值进行排序：

SELECT * FROM games ORDER BY name ASC

But how can I sort the values of id column and save changes in table? Please, help.

但是如何对 id 列的值进行排序并保存表中的更改？请帮忙。

回答by Umbrella

You would have to use a second table

你将不得不使用第二张桌子

1. create a new table games2with the same structure as your gamestable, making sure the ID is auto-incrementing

   CREATE TABLE `games2` LIKE `games`;
   

2. copy the data, sorted, into games2

   INSERT INTO `games2` (`Name`, `SomeDescription`) SELECT `Name`, `SomeDescription` FROM `games` ORDER BY `Name`
   

3. drop or move the old table

   -- DROP TABLE `games`;
   -- or
   RENAME TABLE `games` TO `games1`;
   

4. rename new table to old name

   RENAME TABLE `games2` TO `games`;
   

1. 创建一个games2与表结构相同的新表games，确保 ID 是自动递增的

   CREATE TABLE `games2` LIKE `games`;
   

2. 将已排序的数据复制到 games2

   INSERT INTO `games2` (`Name`, `SomeDescription`) SELECT `Name`, `SomeDescription` FROM `games` ORDER BY `Name`
   

3. 删除或移动旧表

   -- DROP TABLE `games`;
   -- or
   RENAME TABLE `games` TO `games1`;
   

4. 将新表重命名为旧名称

   RENAME TABLE `games2` TO `games`;
   

These steps will result in what you want.

这些步骤将产生您想要的结果。

回答by Anthony Faull

You can use the ROW_NUMBER ranking function to renumber the rows.

您可以使用 ROW_NUMBER 排名函数对行重新编号。

SELECT UnsortedId = id
, SortedId = ROW_NUMBER() OVER (ORDER BY g.name, g.id)
FROM games 

回答by Shashvat Jayakrishnan

You can use alter table command which is simpler.

您可以使用更简单的alter table 命令。

mysql> ALTER TABLE games ORDER BY name asc;

That's all !

就这样 ！

回答by Hemanth Nukala

it is simple. just use a few codes. I have tried this and it is working. first create a temporary table while sorting values at the same time.

很简单。只需使用一些代码。我试过这个，它正在工作。首先创建一个临时表，同时对值进行排序。

    create table new as select * from games order by name;

and then drop the games table using,

然后使用删除游戏桌，

    drop table games;

now create games table again with same properties and data as in new (where sorting here is an optional) using,

现在使用与 new 相同的属性和数据再次创建游戏表（这里的排序是可选的），使用，

    create table games as select * from new order by name;

now drop the temp table 'new'

现在删除临时表“新”

    drop table new;

now check your table. it must be sorted out.

现在检查你的桌子。它必须解决。

回答by CorvetteGuru

This is my fix... stupid simple:

这是我的修复...愚蠢的简单：

1. Extract to temp table

1. 提取到临时表

SELECT SortKeyValue (add any other fixed values, just don't include the existing sequence)INTO #Temp FROM SourceTable;

SELECT SortKeyValue （添加任何其他固定值，只是不包括现有序列）INTO #Temp FROM SourceTable;

1. Empty the table:

2. 清空表：

TRUNCATE TABLE SourceTable

截断表源表

1. Reinsert:

3. 重新插入：

INSERT INTO SourceTable (SortKeyValue, DisplayOrder (plus other fields)) SELECT SortKeyValue, ROW_NUMBER() OVER (ORDER BY SortKeyValue) AS DispOrd (plus other fields)FROM #Temp;

INSERT INTO SourceTable (SortKeyValue, DisplayOrder (plus other fields)) SELECT SortKeyValue, ROW_NUMBER() OVER (ORDER BY SortKeyValue) AS DispOrd (加上其他字段)FROM #Temp;

1. Done!

4. 完毕！

回答by Taryn

you can sort on two fields at the same time.

您可以同时对两个字段进行排序。

SELECT *
FROM games
ORDER BY id DESC, name ASC

I don't understand what you mean by save changes in the table. The ORDER BY, is just changing the display that you are looking at it doesn't change the data in the table unless you perform an UPDATE.

我不明白你在表中保存更改是什么意思。的ORDER BY，只是改变你正在寻找它，除非你执行不改变表中的数据显示UPDATE。

If you are unfamiliar with SQL, there are a lot of tutorials online that can help:

如果您不熟悉 SQL，网上有很多教程可以提供帮助：

SQL Course

SQL课程

Beginner SQL Tutorial

初学者 SQL 教程

SQL Tutorial - Learn SQL

SQL 教程 - 学习 SQL

Edit based on the comments, this can be done in one step:

根据评论进行编辑，这可以一步完成：

create table #temp
(
    id int,
    name varchar(50),
    somedesc varchar(50)
)

insert into #temp values (1, 'best', 'desc1')
insert into #temp values (2, 'worth', 'desc2')
insert into #temp values (3, 'good', 'desc3')

update t1
SET t1.id = t2.rn
    , t1.somedesc = t1.somedesc
FROM #temp t1
JOIN 
(
    select id, name, somedesc, row_number() over(order by name, id) as rn
    from #temp 
) t2
on t1.id = t2.id

select *
from #temp
order by id

drop table #temp

回答by Apache

SQL> SELECT * FROM games ORDER BY ID,NAME;

SQL> SELECT * FROM games ORDER BY ID,NAME;