mysql - 如何使用数字和标记对文本字段进行排序

Question

我有下表:

 +----+-----------------------------------------+----------+
 | id | sort                                    |  channel |
 +----+-----------------------------------------+----------+
 | m2 | ["m2","1","2","11","m4","m3","m5","m1"] | E        |
 | 2  | ["m2","1","2","11","m4","m3","m5","m1"] | H2       |
 | 1  | ["m2","1","2","11","m4","m3","m5","m1"] | H1       |
 | 11 | ["m2","1","2","11","m4","m3","m5","m1"] | H3       |   
 | m4 | ["m2","1","2","11","m4","m3","m5","m1"] | S1       |
 | m3 | ["m2","1","2","11","m4","m3","m5","m1"] | C3       |
 | m5 | ["m2","1","2","11","m4","m3","m5","m1"] | M4       |
 | m1 | ["m2","1","2","11","m4","m3","m5","m1"] | G6       |
 +----+-----------------------------------------+----------+

使用 MySQL 查询:

 SELECT id, sort, channel FROM (SELECT s.id, c.sort, s.channel FROM streams s, channels c WHERE JSON_SEARCH(s.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', s.id) IS NOT NULL UNION SELECT m.marker, c.sort, m.channel FROM markers m, channels c, streams s WHERE JSON_SEARCH(m.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.bouquet, 'one', 1) IS NOT NULL AND JSON_SEARCH(c.sort, 'one', m.marker) IS NOT NULL) ch ORDER BY LOCATE(id, CONCAT("'", sort, "'"));

我得到了上面的表格结果，我需要使用排序字段对 id 进行排序...所以输出需要是:

| id |
------
  m2
  1
  2
  11
  m4
  m3
  m5
  m1

我认为问题出在: ORDER BY LOCATE(id, CONCAT("'", sort, "'")) 但我尝试修改查询但没有获得所需的输出成功...我需要做什么才能获得正确的输出？

Answer 1

使用这个:

ORDER BY LOCATE(CONCAT('"', id, '"'), sort)

这些值包含双引号，因此连接必须将它们括在双引号而不是单引号中。
查看简化版demo .