select name,id from 表名
如果更改表结构:
alter table 表名 modify column id int auto_increment after name
比如:
#建立测试表 t1
mysql>create table t1(id int auto_increment,name varchar(20),primary key(id))engine=innodb,default charset=utf8
Query OK, 0 rows affected (0.11 sec)
#插入3条数据
mysql>insert into t1(name) values ("aa"),("bb"),("cc")
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>select * from t1
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+
3 rows in set (0.00 sec)
#结果集中排列name到id前
mysql>select name,id from t1
+------+----+
| name | id |
+------+----+
| aa | 1 |
| bb | 2 |
| cc | 3 |
+------+----+
3 rows in set (0.00 sec)
#改变表结构,使name在id前
mysql>alter table t1 modify column id int auto_increment after name
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>select * from t1
+------+----+
| name | id |
+------+----+
| aa | 1 |
| bb | 2 |
| cc | 3 |
+------+----+
3 rows in set (0.00 sec)
mysql>desc t1
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| name | varchar(20) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+-------------+------+-----+---------+----------------+
实现的话,貌似主要是两个环节1 先得到用户的两个相邻点的经纬度。
先把表处理成带唯一标识字段的(uid),便于下一步取出经纬度。表T(uid,user_id,latitude,longitude)
生成一个视图,取得两个相邻点经纬度。
create view vt as
select A.user_id,A.latitude latitudeA,A.longitude longitudeA
,B.latitude latitudeB,B.longitude longitudeB
from T A inner join T B
ON A.user_id=B.user_id AND A.uid<B.uid
2 找个经纬度计算距离的公式(据说有好几种算法),在查询中动态生成或编程中计算生成
select user_id, acos( cos(latitudeA)*cos(longitudeA) * cos(latitudeB)*cos(longitudeB)
+cos(latitudeA)*sin(longitudeA) * cos(latitudeB)*sin(longitudeB)
+sin(latitudeA)*sin(latitudeB)) dis
from vt
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)