mysql 查询结果 字段用逗号拼接供where in查询

### 实现 MySQL 左连接并将结果字段用逗号拼接 为了实现在 MySQL 中使用 `LEFT JOIN` 查询并将多个行的结果字段用逗号拼接成一个字符串，可以利用聚合函数 `GROUP_CONCAT()` 来完成这一操作。该函数能够有效地将多行数据汇总到单个字符串中。 对于给定场景中的需求——即在一个表中有存储了其他表主键 ID 的字段（这些 ID 使用逗号分隔），可以通过先解析这个字段再执行联结来获取所需的信息。具体做法如下： #### 解决方案一：使用 FIND_IN_SET 函数配合 GROUP_CONCAT() 当存在两个表格 A 和 B 时，其中 A 表有一个名为 dept_id 的列用于保存来自 B 表的主键 id 列表，并且是以逗号分割的形式存储，则可通过以下 SQL 语句实现查询目的[^1]： ```sql SELECT a.*, GROUP_CONCAT(b.name SEPARATOR ', ') AS names FROM table_a a LEFT JOIN table_b b ON FIND_IN_SET(b.id, a.dept_id) GROUP BY a.id; ``` 这段代码实现了对表A每条记录与其对应于表B的所有名称进行组合，并以逗号加空格作为分隔符返回一个单一字符串形式的名字列表。 #### 方案二：借助子查询与临时表处理复杂情况 如果遇到更复杂的业务逻辑或性能考虑的情况下，可能需要创建一个中间层来进行转换工作。这通常涉及到构建一个派生表，在那里每个原始ID都被单独提取出来以便更好地与其他表建立关系[^2]。 下面是一个较为通用的例子展示这种方法的应用方式： ```sql WITH RECURSIVE split_ids (id, value) AS ( SELECT id, TRIM(SUBSTRING_INDEX(dept_id,',',1)) as value FROM table_a UNION ALL SELECT s.id, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.dept_id,',', LENGTH(a.dept_id)-LENGTH(REPLACE(a.dept_id,',',''))+s.n), ',', -1)) FROM table_a a INNER JOIN (SELECT @row := 0) r, LATERAL(SELECT @row := @row + 1 n) t(s) WHERE SUBSTRING_INDEX(a.dept_id,',', LENGTH(a.dept_id)-LENGTH(REPLACE(a.dept_id,',',''))+t.n) != '' AND s.n <= LENGTH(a.dept_id)-LENGTH(REPLACE(a.dept_id,',','')) ), final_result AS( SELECT si.id, GROUP_CONCAT(DISTINCT b.name ORDER BY b.name ASC SEPARATOR ', ') OVER(PARTITION BY si.id) name_list FROM split_ids si LEFT JOIN table_b b ON CAST(si.value AS SIGNED)=b.id ) SELECT DISTINCT fr.* FROM final_result fr; ``` 上述脚本首先定义了一个递归公用表达式 CTE (`split_ids`) 将部门IDs分解为独立项；接着再次运用CTE(`final_result`) 对应地收集关联信息并通过窗口函数 `OVER` 子句确保相同源记录下的目标字段被正确聚集在一起形成最终输出。

### 如何在 MySQL 中拆分由逗号拼接的字符串 在 MySQL 中，如果需要将一个由逗号分割的字符串拆分为多行或多列，可以利用一些内置函数或者通过创建自定义存储过程来实现这一目标。以下是几种常见的方法： #### 方法一：使用 `FIND_IN_SET` 和 `SUBSTRING_INDEX` 可以通过组合使用 `FIND_IN_SET` 函数和 `SUBSTRING_INDEX` 来逐一分割字符串中的每一部分。 ```sql SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d', ',', numbers.n), ',', -1) AS value FROM ( SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) numbers WHERE numbers.n <= LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1; ``` 上述查询会返回如下结果[^4]: | value | |-------| | a | | b | | c | | d | 此方法的核心在于生成一组连续的数字（通常称为序列表），并将其用于逐步提取字符串的不同部分。 --- #### 方法二：使用递归 CTE（适用于 MySQL 8.0 及以上版本） 从 MySQL 8.0 开始支持递归公用表表达式 (CTE)，这使得处理此类问题更加简洁高效。 ```sql WITH RECURSIVE split_cte AS ( SELECT 'a,b,c,d' AS str, NULL AS part, 0 AS idx UNION ALL SELECT SUBSTRING(str, INSTR(str, ',')+1), IF(INSTR(str, ',')>0, LEFT(str, INSTR(str,',')-1), str), idx + 1 FROM split_cte WHERE str != '' ) SELECT TRIM(part) AS value FROM split_cte WHERE part IS NOT NULL; ``` 该查询同样会产生以下输出: | value | |-------| | a | | b | | c | | d | 这种方法的优点是可以动态适应不同长度的输入字符串，并且无需手动构建辅助数据集。 --- #### 方法三：借助临时表或视图 对于更复杂的情况，可能需要先将原始记录存入一张中间表格中，然后再执行进一步操作。例如，假设存在这样一条记录 `(id=1, col='apple,banana,cherry')` ，我们希望按逗号分开后得到三条独立的结果项，则可采用下面的方式完成转换工作： ```sql CREATE TEMPORARY TABLE temp_split ( id INT PRIMARY KEY AUTO_INCREMENT, orig_id INT, item VARCHAR(255) ); INSERT INTO temp_split(orig_id, item) SELECT t.id, s.value FROM your_table t JOIN JSON_TABLE(CONCAT('["', REPLACE(t.col, ',', '","'), '"]'), '$[*]' COLUMNS(value VARCHAR(255) PATH '$')) s; -- 查询最终结果 SELECT * FROM temp_split ORDER BY orig_id ASC; ``` 这里引入了 `JSON_TABLE()` 功能作为桥梁连接起原字段与新产生的各个片段之间的关系链路[^5]。 --- ### 总结 尽管 MySQL 自身并未提供专门针对字符串切割成行的功能指令，但凭借灵活运用现有工具仍能达成预期效果。具体选择哪种方案取决于实际应用场景以及数据库系统的兼容程度等因素考量。