MySQL字符串函数 [英] MySQL string functions
问题描述
我有一个包含以下数据的表格:
reservno || icode ||地点
00004 || 00021 ||保和省 - 宿务
00004 || 00022 ||宿务 - 马尼拉
00004 || 00014 ||马尼拉 - 保和
我使用此查询来检索位置的拼接值。
$ b
从location_list中选择GROUP_CONCAT(位置),其中reservno ='00004';
查询结果如下所示:
$ Bo $ - b
但是我想要做的是让查询看起来像这样: Bohol - 宿务 - 马尼拉 - Bohol
。我想合并这样的结果。我怎样才能做到这一点?我对MySQL字符串函数并不熟悉,所以我需要一些关于如何使其工作的想法。任何帮助将不胜感激。非常感谢! GROUP_CONCAT 函数:
SELECT GROUP_CONCAT(IF((@ var_ctr:= @var_ctr + 1)= @cnt,
位置,
SUBSTRING_INDEX(location,' - ',1)
)
ORDER BY loc_id ASC
SEPARATOR' - ')AS位置
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 ||宿务
00004 || 00022 ||宿务||马尼拉
00004 || 00014 ||马尼拉||保和省
I have a a table with the following data:
reservno || icode || location
00004 || 00021 || Bohol - Cebu
00004 || 00022 || Cebu - Manila
00004 || 00014 || Manila - Bohol
I use this query to retrieve the concatenated value of location.
SELECT GROUP_CONCAT(location) from location_list where reservno='00004';
The query result looks like this:
GROUP_CONCAT(location)
Bohol - Cebu,Cebu - Manila,Manila - Bohol
But what I want to do is for the query to look like this: Bohol - Cebu - Manila - Bohol
. I would like to merge the result like that. How can I achieve this? I'm not that familiar with MySQL string functions so I need some ideas on how to make this work. Any help will be appreciated. Thanks a lot!
You need to use SEPARATOR
in GROUP_CONCAT
function:
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';
Example: SQLFIDDLE
It's not a good practice to store multiple values in same column, Better way could be:
reservno || icode || location_from || location_to
00004 || 00021 || Bohol || Cebu
00004 || 00022 || Cebu || Manila
00004 || 00014 || Manila || Bohol
这篇关于MySQL字符串函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!