mysql5.1.72中如何按地址排序?地址构成为小区名p栋q门r号

软件和网站开发以及相关技术探讨
回复
alober
帖子: 135
注册时间: 2010-07-13 17:04

mysql5.1.72中如何按地址排序?地址构成为小区名p栋q门r号

#1

帖子 alober » 2018-04-17 19:55

用的是mysql 5.1.72。

有一些数据,带有地址列,地址列的部分数据如下:

巷子苑1栋2门502
巷子苑2栋4门509
巷子苑10栋3门1203
凯旋楼3门1201
凯旋楼2门1002
巷子苑B栋2门203
巷子苑B栋3门506

现在想按地址排序。地址构成一般是小区名+p栋+q门+r号(号字省略),但有一些特殊情况:有的地址如凯旋楼,只有一栋楼,所以没有p栋的标识。有的地址是带ABCD这种栋号的,q门也有12门这种的,不一定只有一位数字。现在想让这些地址首先按小区名排序(文本方式),同一小区的按p栋排序(数字方式),同一栋的按q门排序(数字方式)。

请教这个应该怎么用sql做?注意只能用sql。
头像
astolia
论坛版主
帖子: 6396
注册时间: 2008-09-18 13:11

Re: mysql5.1.72中如何按地址排序?地址构成为小区名p栋q门r号

#2

帖子 astolia » 2018-04-22 0:06

基本思路就是自己把需要的信息用正则之类的截取出来按顺序order by。
mysql8.0+可以直接用REGEXP_REPLACE,mariadb10.0.5+也支持
order by REGEXP_REPLACE(address, '[0-9].*', ''), CAST(REGEXP_REPLACE(REGEXP_REPLACE(address, '^[^0-9]+', ''), '栋.*', '') AS UNSIGNED INTEGER), ....
至于那些可有可无的栋之类,用 if address like '%栋%' then REGEXP_REPLACE(...) else '' 这种条件语句来控制

你还在用5.1,那就只能用那些查找替换等一般字符串操作来模拟正则了,想想就恶心。
alober
帖子: 135
注册时间: 2010-07-13 17:04

Re: mysql5.1.72中如何按地址排序?地址构成为小区名p栋q门r号

#3

帖子 alober » 2018-04-22 8:10

astolia 写了:基本思路就是自己把需要的信息用正则之类的截取出来按顺序order by。
mysql8.0+可以直接用REGEXP_REPLACE,mariadb10.0.5+也支持
order by REGEXP_REPLACE(address, '[0-9].*', ''), CAST(REGEXP_REPLACE(REGEXP_REPLACE(address, '^[^0-9]+', ''), '栋.*', '') AS UNSIGNED INTEGER), ....
至于那些可有可无的栋之类,用 if address like '%栋%' then REGEXP_REPLACE(...) else '' 这种条件语句来控制

你还在用5.1,那就只能用那些查找替换等一般字符串操作来模拟正则了,想想就恶心。
用5.1的原因是他们的服务器放在了windows xp上,装不了高版本,我还问了能不能装个win 7之类的,但是他们那个电脑不只充当服务器,还要做其它业务,而其它业务有xp上的专用软件,win 7用不了。所以很多麻烦事,连做索引都必须单独弄出来,用不了函数类型的索引。现在就直接按地址排序了,1和10都排在了一起。

试了一下 MariaDB,有正则做起来还是简单:

代码: 全选

SELECT address,
regexp_replace(address,'(.*?)((\\d+)栋)?(\\d+)门(\\d+)','\\1') AS zone,
CAST(regexp_replace(address,'(.*?)((\\d+)栋)?(\\d+)门(\\d+)','\\3') AS UNSIGNED) AS building,
CAST(regexp_replace(address,'(.*?)((\\d+)栋)?(\\d+)门(\\d+)','\\4') AS UNSIGNED) AS unit
FROM person
ORDER BY zone, building, unit
回复