首先,我想指出的是,我几乎尝试了所有方法。自过去 8 小时以来,我一直在尝试按顺序排列我的列表,我已经应用了这里找到的十几种解决方案。
下面是示例数据的 SQL 摆弄。我找到了一个页面,可以按正确的顺序对我的列表进行排序,那就是:
1
2
2.B3
5
9
10 A-1
10 A-3
10 B-4
10 B-5
11
12
B3-43
B3-44
B3 - 48
B3 - 49
Basztowa 3
Basztowa 4
Basztowa 5
Basztowa 7
Basztowa 9
D.1
D.2
D.10
D.11
D.12
Kabaty ul. Pod lipą 4
但是我无法使用MySQL重现它。
我将不胜感激任何帮助,因为我没有更多的想法。我考虑使用 PHP 对我的列表进行排序,但据我所知,DBMS 针对这孩子操作进行了优化,所以如果可能的话,我想避免使用 PHP 执行此操作。
@UPDATE
多亏了@Jakumi我创建了两个功能来帮助我解决问题。
您需要创建一个列以排序友好的格式 (zeropadded_name( 存储您的值,在更新时创建触发器并在名称更改时插入以填充zeropadded_name仅此而已!现在只需按zeropadded_name订购即可享用!
帮助程序函数
-
regex_replace
- 它的任务是通过删除所有非字母数字字符来帮助我们清理值。 -
lpad_numbers
- 填充字符串中的每个数字。这有点丑陋,因为我不太了解MySQL功能,但是嘿,它的工作速度非常快。
例:
SELECT lpad_numbers(regex_replace('[^a-zA-Z0-9]', ' ', 'B3 - A-5'));
#B0003A0005
DROP FUNCTION IF EXISTS regex_replace;
CREATE FUNCTION `regex_replace`(
pattern VARCHAR(1000)
CHARSET utf8
COLLATE utf8_polish_ci,
replacement VARCHAR(1000)
CHARSET utf8
COLLATE utf8_polish_ci,
original VARCHAR(1000)
CHARSET utf8
COLLATE utf8_polish_ci
) RETURNS varchar(1000) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000)
CHARSET utf8
COLLATE utf8_polish_ci;
DECLARE ch VARCHAR(1)
CHARSET utf8
COLLATE utf8_polish_ci;
DECLARE i INT;
SET i = 1;
SET temp = '';
IF original REGEXP pattern
THEN
loop_label: LOOP
IF i > CHAR_LENGTH(original)
THEN
LEAVE loop_label;
END IF;
SET ch = SUBSTRING(original, i, 1);
IF NOT ch REGEXP pattern
THEN
SET temp = CONCAT(temp, ch);
ELSE
SET temp = CONCAT(temp, replacement);
END IF;
SET i = i + 1;
END LOOP;
ELSE
SET temp = original;
END IF;
RETURN temp;
END;
DROP FUNCTION IF EXISTS lpad_numbers;
CREATE FUNCTION `lpad_numbers`(str VARCHAR(256)) RETURNS varchar(256) CHARSET utf8 COLLATE utf8_polish_ci
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret VARCHAR(256) DEFAULT '';
DECLARE num VARCHAR(256) DEFAULT '';
DECLARE c CHAR(1);
IF str IS NULL
THEN
RETURN "";
END IF;
SET len = CHAR_LENGTH(str);
REPEAT
BEGIN
SET c = MID(str, i, 1);
IF c BETWEEN '0' AND '9'
THEN
SET num = c;
SET i = i + 1;
REPEAT
BEGIN
SET c = MID(str, i, 1);
SET num = CONCAT(num, c);
SET i = i + 1;
END;
UNTIL c NOT BETWEEN '0' AND '9' END REPEAT;
SET ret = CONCAT(ret, LPAD(num, 4, '0'));
ELSE
SET ret = CONCAT(ret, c);
SET i = i + 1;
END IF;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END;
底层结构进行拆分
从技术上讲,mysql 排序机制工作正常,但您的字符串格式错误。数据的底层结构类似于以下内容(为了便于与示例关联,保留Original
列(:
alpha1 num1 alpha2 num2 ... Original
1 1
2 2
2 B 3 2.B3
5 5
9 9
10 A 1 10 A-1
10 A 3 10 A-3
10 B 4 10 B-4
10 B 5 10 B-5
11 11
12 12
B 3 43 B3-43
B 3 44 B3-44
B 3 48 B3 - 48
B 3 49 B3 - 49
Basztowa 3 Basztowa 3
Basztowa 4 Basztowa 4
Basztowa 5 Basztowa 5
Basztowa 7 Basztowa 7
Basztowa 9 Basztowa 9
D 1 D.1
D 2 D.2
D 10 D.10
D 11 D.11
D 12 D.12
如果您现在使用ORDER BY alpha1, num1, alpha2, num2
对它们进行排序,它们将按照您想要的方式排序。但是已经"格式化"的版本(Original
列(不容易排序,因为应按字母顺序排序的部分和应按数字排序的部分混合在一起。
零填充
有一个不太广泛的替代方案只需要一个额外的列,你假设没有数字超过10000,你现在可以用零填充的版本替换每个数字(不是数字!(,所以10 A-1
会变得0010A0001
(显然是0010
和A
和0001
(,但我不认为这是在ORDER BY
语句中即时进行的。
但对于此示例,零填充版本(假设:每个数字<10000(:
Original Zeropadded
1 0001
2 0002
2.B3 0002B0003
5 0005
9 0009
10 A-1 0010A0001
10 A-3 0010A0003
10 B-4 0010B0004
10 B-5 0010B0005
11 0011
12 0012
B3-43 B00030043
B3-44 B00030043
B3 - 48 B00030048
B3 - 49 B00030049
Basztowa 3 Baztowa0003
Basztowa 4 Baztowa0004
Basztowa 5 Baztowa0005
Basztowa 7 Baztowa0007
Basztowa 9 Baztowa0009
D.1 D0001
D.2 D0002
D.10 D0010
D.11 D0011
D.12 D0012
这将可以通过ORDER BY zeropadded
根据您的意愿进行排序。
所以最后,你可能不得不在php中排序或创建更多的列,通过重新格式化/清理/拆分你的输入来帮助你排序。
更新
零填充解释(简体(
零填充背后的主要思想是数字的自然格式与计算机中的格式不同。在计算机中,数字 2 实际上是数字序列 0..0002(因此包括前导零(类似于 10 (0..0010(。当计算机比较数字时,它会从左到右,直到找到不同的数字:
0...0002
0...0010
======!. (the ! marks the point where the first digit is different)
然后它将确定哪个数字更大或更小。在这种情况下,0 <1,因此 2 <10。(当然,计算机使用二进制,但这不会改变这个想法(。
现在,字符串在技术上是一个字符序列。字符串比较的工作方式略有不同。当比较两个字符串时,它们不是(左(填充的,因此每个字符串的第一个字符实际上是第一个字符而不是填充(例如空格(。所以从技术上讲,字符串A10
是字符序列A
、1
和0
。由于使用了字符串比较,因此它比A2
"小",因为字符串比较不会将数字视为数字,而是将字符(即数字(视为字符:
A10
A2
=! (the ! marks the point where the first character is different)
而且因为1
<2
作为角色,A10
><A2
.现在为了规避这个问题,我们强制字符串中数字的格式与数字比较中的格式相同,方法是将数字填充到相同的长度,根据数字的位置值对齐数字:>
A0010
A0002
===!. (the ! marks the point where the first character is different)
现在,它实际上与您在数值比较中期望的比较相同。但是,您必须对数字的最大长度做出一些假设,以便您可以适当地选择填充。如果没有这个假设,你就会遇到问题。
剩下的唯一(逻辑(点是:当比较的字符串具有字母字符而另一个字符串具有数字时,填充会更改什么?答案是:没有。我们不会将数字更改为字母,并且数字比字母小,因此在这种情况下,所有内容都保持相同的顺序。
零填充的效果是:我们通过根据数字字符的值对齐数字字符来调整字符串中的"数字"比较,使其类似于实数比较。
SELECT name FROM realestate ORDER BY name ASC;
这应该在字母数字数据中对您的列表进行排序...我没有看到问题。
编辑:好的,我仍然不知道我是否真的理解这个问题的目标是什么(是为了比赛吗?(,但我可以提交这个"扭曲"的查询(我希望我永远不会在我的职业生涯中使用(:
SELECT name FROM realestate
ORDER BY IF(SUBSTRING(name, 1, 2) REGEXP '[A-Z]', 100000, CAST(name AS UNSIGNED)) ASC,
SUBSTRING(name, 1, 2) ASC,
CAST(SUBSTRING(name FROM LOCATE('.', name)+1) AS UNSIGNED) ASC,
REPLACE(name, ' ', '') ASC;
也许有人可以找到更简单的方法,因为我承认我的答案有点复杂。但是,Kamil和Jakumi的解决方案更加棘手和复杂。