按字符串中的数字排序现有数据库

Question

这是一场噩梦。我正在为一个现有的数据库做前端工作，我必须跳过循环，以确保数据以正确的顺序显示。如果我能按Id排序，我的生活就会简单得多，但Id与数据几乎没有关联。

我的意思是

ID   DATA
357  "7-1-5: Sensitive Information I can't share"
2521 "30-2-8-17: Yet more sensitive Information"
6002 "9-30: There's a 10 behind the colon, because I hate you"
8999 "2-2-4: This was populated in no particular order"
9001 "30-3: More Info."

我正试着像这样点菜

ID   DATA 
0001 "2-2-4: This was populated in no particular order"
0002 "7-1-5: Sensitive Information I can't share"
0003 "9-30: There's a 10 behind the colon, because I hate you"
0004 "30-2-8-17: Yet more sensitive Information"
0005 "30-3: More Info."

基本上，我需要它按照每一个1到2位数排序，用破折号隔开，一次又一次，这样1-3在1-2-1之后，在1-1-50之后。

正如我在一开始所说的，我是一个前卫，所以在MySql中执行的东西比我一个人做的更多。任何帮助都将不胜感激。

编辑:我刚刚意识到在一个单独的表中有外键指向这个表，这使得事情变得更糟了。

Answer 1

请尝试以下查询：

SELECT col
FROM yourTable
ORDER BY SUBSTRING(col, INSTR(col, '"') + 1, INSTR(col, ':') - INSTR(col, '"') - 1)

SUBSTRING(...)子句中的ORDER BY项只从文本中提取in。想必您希望它们从左到右进行数字排序。即使它们是varchar，数值排序仍然有效。

对于示例数据，这将产生以下输出：

ID 8999 DATA "2-2-4: This was populated in no particular order"
ID 2521 DATA "30-2-8-17: Yet more sensitive Information"
ID 357  DATA "7-1-5: Sensitive Information I can't share"
ID 6002 DATA "9-30: There's a 10 behind the colon, because I hate you"

在编写这个答案时，可是我在MySQL工作台上测试了这个查询，而且它似乎运行得很好。

编辑：

如果要为每个记录分配一个新ID，则创建一个具有ID列的新表(newTable)，该列为自动增量。然后可以使用INSERT INTO ... SELECT和上面的ORDER BY逻辑填充表。ID字段应由MySQL自动递增。

INSERT INTO newTable (`id`, `col`)
SELECT NULL, col
FROM yourTable
ORDER BY SUBSTRING(col, INSTR(col, '"') + 1, INSTR(col, ':') - INSTR(col, '"') - 1)

Answer 2

类似这样的东西应该可以工作，但它非常微妙；在外部SELECT ( *) 之后计算的所有字段都必须按照精确顺序执行。注意，计算别名为nl#__、p#__和r# (r0__除外)。因此，查询并不像最初显示的那样复杂。

SELECT *
    , @r := dataOrd AS r0 -- @r is "remaining string"
    , @nextSep := INSTR(@r, '-') AS nl1
    , CAST(CASE @nextSep WHEN 0 THEN @r ELSE SUBSTR(@r, 1, @nextSep-1) END AS UNSIGNED) AS p1
    , @r := CASE @nextSep WHEN 0 THEN '' ELSE SUBSTRING(@r, @nextSep+1) END AS r1
    , @nextSep := INSTR(@r, '-') AS nl2
    , CAST(CASE @nextSep WHEN 0 THEN @r ELSE SUBSTR(@r, 1, @nextSep-1) END AS UNSIGNED) AS p2
    , @r := CASE @nextSep WHEN 0 THEN '' ELSE SUBSTRING(@r, @nextSep+1) END AS r2
    , @nextSep := INSTR(@r, '-') AS nl3
    , CAST(CASE @nextSep WHEN 0 THEN @r ELSE SUBSTR(@r, 1, @nextSep-1) END AS UNSIGNED) AS p3
    , @r := CASE @nextSep WHEN 0 THEN '' ELSE SUBSTRING(@r, @nextSep+1) END AS r3
    , @nextSep := INSTR(@r, '-') AS nl4
    , CAST(CASE @nextSep WHEN 0 THEN @r ELSE SUBSTR(@r, 1, @nextSep-1) END AS UNSIGNED) AS p4
    , @r := CASE @nextSep WHEN 0 THEN '' ELSE SUBSTRING(@r, @nextSep+1) END AS r4
FROM
(
    SELECT *, SUBSTR(`DATA`, 1, INSTR(`DATA`, ':') - 1) AS dataOrd
    FROM yourTable
) AS sepSubQ
ORDER BY p1, p2, p3, p4
;

从技术上讲，上一个@r赋值(别名化的r4)是不必要的，但是它完成了需要重复的模式，如果您需要处理超过4个排序“部件”；在这种情况下，您只需要重复最后三个字段计算(使用递增的别名)。

如果您想摆脱“工作”字段，可以将其包装在另一个外部查询中，只从您想要的原始表中选择字段，从上面的查询中选择pX字段；从技术上讲，您甚至不需要选择pX字段，因为这个查询已经执行了顺序，或者可以在包装器中完成而不选择它们。

SELECT `ID`, `DATA`
FROM ([the query above]) AS subQ
ORDER BY p1, p2, p3, p4
;