Mysql中可以给用order by取出的数据赋新的id号吗？

Question

有没有sql高手知道这样的问题呢？紧急求助～～

Answer 1

alter table tablename
add colname int identity(1,1)

Answer 2

mysql> select * from tmp;
+----------+--------+
| province | city   |
+----------+--------+
| 北京     | 北京   |
| 河北     | 保定   |
| 河北     | 沧州   |
| 河北     | 承德   |
| 河北     | 邯郸   |
| 河北     | 衡水   |
| 河北     | 廊坊   |
| 河北     | 秦皇岛 |
| 河北     | 石家庄 |
| 河北     | 邢台   |
| 河北     | 张家口 |
| 湖北     | 鄂州   |
| 湖北     | 恩施   |
| 湖北     | 黄冈   |
| 湖北     | 黄石   |
| 湖北     | 江汉   |
| 湖北     | 荆门   |
| 湖北     | 荆州   |
| 湖北     | 潜江   |
| 湖北     | 神农架 |
| 湖北     | 十堰   |
| 湖北     | 随州   |
| 湖北     | 天门   |
| 湖北     | 武汉   |
| 湖北     | 仙桃   |
| 湖北     | 咸宁   |
| 湖北     | 襄樊   |
| 湖北     | 孝感   |
| 湖北     | 宜昌   |
+----------+--------+
29 rows in set (0.00 sec)mysql> set @id:=0;
Query OK, 0 rows affected (0.01 sec)mysql> select @id:=@id+1,city from tmp;
+------------+--------+
| @id:=@id+1 | city   |
+------------+--------+
|          1 | 保定   |
|          2 | 北京   |
|          3 | 沧州   |
|          4 | 承德   |
|          5 | 鄂州   |
|          6 | 恩施   |
|          7 | 邯郸   |
|          8 | 衡水   |
|          9 | 黄冈   |
|         10 | 黄石   |
|         11 | 江汉   |
|         12 | 荆门   |
|         13 | 荆州   |
|         14 | 廊坊   |
|         15 | 潜江   |
|         16 | 秦皇岛 |
|         17 | 神农架 |
|         18 | 十堰   |
|         19 | 石家庄 |
|         20 | 随州   |
|         21 | 天门   |
|         22 | 武汉   |
|         23 | 仙桃   |
|         24 | 咸宁   |
|         25 | 襄樊   |
|         26 | 孝感   |
|         27 | 邢台   |
|         28 | 宜昌   |
|         29 | 张家口 |
+------------+--------+
29 rows in set (0.02 sec)

Answer 3

select @id:=@id+1 as id,city from tmp;