用于子查询的MySQL拆分列值?

I am using a plugin for OpenCart that is combining a number of ids into a single string and storing them in a single column. Example values are 21760 and 6772:15499:15497. The number of concatenated ids can range from 1 to 5 (most are two or three ids).

我正在使用一个OpenCart的插件，它将多个id组合成一个字符串，并将它们存储在单个列中。示例值为21760和6772:15499:15497。连接的id的数量可以从1到5(大多数是两个或三个id)。

I am looking to get the name values of these ids in a query result, concatenated in the same order as the ids are stored in.

我希望在查询结果中获得这些id的名称值，并以与ids存储在相同的顺序中的顺序连接。

For simplicities sake assume the two tables only have two columns (in reality there is a third table that I want to do a join on, but that is irrelevant to the immediate issue):

为了简单起见，我们假设这两个表只有两个列(实际上，我想要加入的是第三个表，但这与当前的问题无关):

Table a:

表一:

|id    | name        |
|6773  | Google      |
|15497 | Apple       |
|15500 | Microsoft   |
|...   | ...         |

and

和

Table b:

表2:

|id    | var                 |
|123   | 6773:15500:15497    |
|543   | 45688               |
|22311 | 885:2588            |
|...   | ...                 |

Based on a var of 6773:15500:15497 I want the output of the query to be Google:Microsoft:Apple

基于6773:15500:15497的var，我希望查询的输出是谷歌:Microsoft:Apple

I have no idea of where to begin with this sort of query.

我不知道从哪里开始这种查询。

3 个解决方案

#1

It is not a good idea to keep data this way.

以这种方式保存数据不是一个好主意。

The first idea come into my head is : http://sqlfiddle.com/#!2/1dd77/4

我的第一个想法是:http://sqlfiddle.com/#!2/1dd77/4

SELECT b.*, GROUP_CONCAT(a.name SEPARATOR ':')
FROM table2 as b
LEFT JOIN table1 as a
on b.var = a.id 
  OR  b.var regexp(CONCAT('^',a.id,':'))
  OR  b.var regexp(CONCAT(':',a.id,':'))
  OR  b.var regexp(CONCAT(':',a.id,'$'))
 GROUP BY b.id

EDIT 1

编辑1

Ordered variant: http://sqlfiddle.com/#!2/1dd77/38

命令变体:http://sqlfiddle.com/ ! 2/1dd77/38

SELECT b.*, GROUP_CONCAT(a.name ORDER BY FIND_IN_SET(a.id, REPLACE(b.var,":",",")) SEPARATOR ':' )
FROM table2 as b
LEFT JOIN table1 as a
on b.var = a.id 
  OR  b.var regexp(CONCAT('^',a.id,':'))
  OR  b.var regexp(CONCAT(':',a.id,':'))
  OR  b.var regexp(CONCAT(':',a.id,'$'))
 GROUP BY b.id

#2

You could try to do a Procedure. First check out how many strings you have separate by a delimiter. Then, split their values based on their position. Then, store the values into a variables. Finally, do the select that you want and return it.

你可以试着做一个程序。首先检查有多少字符串被分隔符分隔。然后，根据他们的位置来划分他们的值。然后，将值存储到一个变量中。最后，执行您想要的选择并返回它。

DELIMITER $$
    CREATE PROCEDURE SPLIT_STR_AND_RETURN(str VARCHAR(255) IN, delimiter VARCHAR(12) IN)
    BEGIN
    -- DEFINE MY VARIABLES
    DEFINE v_count_str INT;
    DEFINE v_index INT DEFAULT 1;
    DEFINE v_id_target CHAR(60);
    DEFINE v_name TEXT;

    -- CREATE A TABLE WHERE I STORE THE VALUES
    DROP TEMPORARY TABLE IF EXISTS stored_values_tmp; -- make sure it doesnt already exist
    CREATE TEMPORARY TABLE stored_values_tmp (
      name_str varchar(120)
    ) ENGINE=memory;

    -- GET HOW MANY IDs I HAVE
    SELECT (LENGTH( str ) - LENGTH( REPLACE( str, delimiter,  '' ) ))+1 INTO v_count_str;
    -- DO A LOOP FOR OBTAINT THE IDs. POSITION = index
    WHILE v_index <= v_count_str DO
      -- SPLIT THE STRING
      SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delimiter, v_index),
           LENGTH(SUBSTRING_INDEX(str, delimiter, v_index -1)) + 1),
           delimiter, '') INTO v_id_target;
      -- GET THE NAME
      SELECT name INTO v_name FROM table_a WHERE id = v_id_target
      -- STORE
      INSERT INTO stored_values_tmp(name_str) VALUES(v_name);
    END WHILE;

    -- GET THE RESULT
    SELECT * FROM stored_values_tmp;

    -- DROP TMP TABLE
    DROP TEMPORARY TABLE stored_values_tmp;
   END$$
DELIMITER ;

Finally, call the procedure:

最后,调用过程:

CALL SPLIT_STR_AND_RETURN('6772:15499:15497',':');

#3

Here is solution and it will work but only if you know for sure that the max number of concatenated ids is 5... If there is six somewhere then you need to extend the query:

这是一种解决方案，它将发挥作用，但前提是您确定连接id的最大数量是5……如果某个地方有六个，那么您需要扩展查询:

SELECT tx.id, CONCAT_WS(':', t1.name, t2.name, t3.name, t4.name, t5.name) as var
FROM (SELECT id, SUBSTRING_INDEX(var, ':', 1) as sub,          
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 0,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 1)), ':') FROM (SUBSTRING_INDEX(var, ':', 2))), '') as sub2,
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 1,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 2)), ':') FROM (SUBSTRING_INDEX(var, ':', 3))), '') as sub3,
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 2,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 3)), ':') FROM (SUBSTRING_INDEX(var, ':', 4))), '') as sub4,
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 3,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 4)), ':') FROM (SUBSTRING_INDEX(var, ':', 5))), '') as sub5
       FROM table2) as tx
LEFT JOIN table1 t1
ON tx.sub = t1.id
LEFT JOIN table1 t2
ON tx.sub2 = t2.id
LEFT JOIN table1 t3
ON tx.sub3 = t3.id
LEFT JOIN table1 t4
ON tx.sub4 = t4.id
LEFT JOIN table1 t5
ON tx.sub5 = t5.id

Here is SQL Fiddle for that http://sqlfiddle.com/#!2/8a2f50/1

这里是http://sqlfiddle.com/#!2/8a2f50/1。

This solution is not most elegant but it will do the job.

这个解决方案不是最优雅的，但它将发挥作用。

Also this

也这

SELECT id, SUBSTRING_INDEX(var, ':', 1) as sub,          
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 0,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 1)), ':') FROM (SUBSTRING_INDEX(var, ':', 2))), '') as sub2,
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 1,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 2)), ':') FROM (SUBSTRING_INDEX(var, ':', 3))), '') as sub3,
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 2,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 3)), ':') FROM (SUBSTRING_INDEX(var, ':', 4))), '') as sub4,
           IF((LENGTH(var) - LENGTH(REPLACE (var, ':', ''))) > 3,  
           TRIM(LEADING CONCAT((SUBSTRING_INDEX(var, ':', 4)), ':') FROM (SUBSTRING_INDEX(var, ':', 5))), '') as sub5
       FROM table2

part of code will return you the table like this

部分代码会像这样返回表。

 +----+---------+---------+---------+---------+---------+
 | id |sub1     |sub2     |sub3     |sub4     |sub5     |  
 +----+---------+---------+---------+---------+---------+
 |123 |6773     |15500    |15479    |         |         |
 +----+---------+---------+---------+---------+---------+
 |543 |15497    |1        |         |         |         |
 +----+---------+---------+---------+---------+---------+
  ...

here is Faddle http://sqlfiddle.com/#!2/8a2f50/2

这是开玩笑http://sqlfiddle.com/ ! 2/8a2f50/2

So you can use it somehow to preform other operation on base table like update, search or something like that...

所以你可以用它在基表上进行其他操作比如更新，搜索等等。

GL!

GL !

#1