MySQL的拆分列值用于子查询? [英] MySQL split column values for use in sub-query?

问题描述

我正在使用OpenCart插件，该插件将多个ID组合为单个字符串，并将其存储在单个列中.示例值为21760和6772:15499:15497.串联ID的数量范围为1到5(大多数是2或3个ID).

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).

我希望在查询结果中获取这些ID的名称值，这些ID的存储顺序与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.

为简单起见，假定两个表只有两列(实际上，我要在第三个表上进行联接，但这与紧迫的问题无关):

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):

表a:

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

和

表b:

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

基于6773:15500:15497的var，我希望查询的输出为Google:Microsoft:Apple

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

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

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

推荐答案

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

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

首先想到的是: 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

编辑1

订购的变体: 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

这篇关于MySQL的拆分列值用于子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！