MySQL字符串函数

Question

我有一个包含以下数据的a表：

reservno || icode || location
00004    || 00021 || Bohol - Cebu
00004    || 00022 || Cebu - Manila
00004    || 00014 || Manila - Bohol

我使用这个查询来检索location的连接值。

SELECT GROUP_CONCAT(location) from location_list where reservno='00004';

查询结果如下所示：

GROUP_CONCAT(location)
Bohol - Cebu,Cebu - Manila,Manila - Bohol

但是我想要做的是让查询看起来像这样：Bohol - Cebu - Manila - Bohol。我想像这样合并结果。我如何才能做到这一点？我对MySQL字符串函数不是很熟悉，所以我需要一些关于如何使其工作的想法。任何帮助都将不胜感激。非常感谢!

Answer 1

您需要在GROUP_CONCAT函数中使用SEPARATOR：

SELECT  GROUP_CONCAT(IF((@var_ctr := @var_ctr + 1) = @cnt, 
                        location, 
                        SUBSTRING_INDEX(location,' - ', 1)
                       ) 
                       ORDER BY loc_id ASC
                       SEPARATOR ' - ') AS locations

FROM location_list, 
     (SELECT @cnt := COUNT(1), @var_ctr := 0 
      FROM location_list 
      WHERE reservno='00004'
     ) dummy
WHERE reservno='00004';

示例：SQLFIDDLE

在同一列中存储多个值不是一种好的做法，更好的方法可能是：

reservno || icode || location_from || location_to
00004    || 00021 || Bohol         || Cebu
00004    || 00022 || Cebu          || Manila
00004    || 00014 || Manila        || Bohol

Answer 2

这里有一种方法。它从您的单个location列中分离出两个位置，对其进行区分，然后将其放回SQL Fiddle中

SELECT GROUP_CONCAT(DISTINCT loc SEPARATOR ' - ')
FROM 
(
    SELECT SUBSTRING_INDEX(location, ' - ', 1) AS loc
    FROM location_list 
    WHERE reservno='00004'
    UNION ALL
    SELECT SUBSTRING_INDEX(location, ' - ', -1) AS loc
    FROM location_list 
    WHERE reservno='00004'
) separatedLocs

不过，你可能在寻找更智能的东西？我感觉“位置”更像是一种“从-到”的东西？我还质疑在location列中存储多个值的决定，如下所示。最好将location1和location2存储为单独的列。

Answer 3

正如the currently accepted answer后来对cause problems所说的那样，这里有另一种选择。它在很大程度上基于the solution by lc.，但负责排序。

首先: SQL表没有定义良好的行顺序。在查询表中的所有行时，这些行将按特定顺序返回，但该顺序未定义，可能与添加行的顺序相关，也可能与添加行的顺序无关，并且可能会意外更改。因此，当您想要以给定的顺序处理行时，您应该添加一个包含某种序列号或smilar的列。这就是为什么lc。关于位置顺序的kept bugging you。

如果您有一个名为seq的列，则可以使用以下查询：

SELECT GROUP_CONCAT(loc SEPARATOR ' - ')
FROM (
 (SELECT 1 half, seq, SUBSTRING_INDEX(location, ' - ', 1) loc
  FROM location_list
  WHERE reservno='00004'
  ORDER BY seq
  LIMIT 1)
 UNION ALL
 (SELECT 2 half, seq, SUBSTRING_INDEX(location, ' - ', -1) loc
  FROM location_list
  WHERE reservno='00004')
 ORDER BY half, seq
) locs

联合将产生不同位置的列表，该列表使用最外层的select组合成单个字符串。联合的第一部分产生路由第一部分的前半部分，而联合的第二部分将给你所有部分的后半部分。到目前为止，联合结果的顺序还没有定义，所以我们需要一个整体的排序规则。我们还需要前半部分的排序规则，这样我们才能真正选择带有limit子句的路由的第一部分。

Here是一个基于Omesh设置的sqlfiddle。由于缺少seq列，它使用icode列来排序。因此，结果与您预期的结果不同，并生成Manila - Bohol - Cebu - Manila。

如果添加了seq列，则必须更改数据库模式，因此最好将其更改为将每个部分的两个端点转换为两个不同的列。使用CONCAT组合列很简单，但是拆分它们会增加开发人员和数据库引擎查询的复杂性。

如果您无法添加序列列，因为您无法控制数据库模式，那么您就有麻烦了。Your comment here表明您总是在寻找循环，但是最好使用map从无序数据中找到这样的循环，这在SQL级别是不容易获得的。如果有必要，您可以通过存储过程来实现这一点，为jurney的每个部分执行一个select，但如果我是您，我更愿意在应用程序级别处理此问题。