如何在SQL中使用嵌套查询实现复杂的字符串拆分_结合系统函数与子查询-SQL-PHP中文网

sql server 2016+ 应用 string_split 需配合 cross apply 而非子查询，因其返回表值函数结果；须用 trim() 处理空格，依赖 ordinal 列（sql server 2022+）保证顺序，不支持多字符分隔符。

如何在sql中使用嵌套查询实现复杂的字符串拆分_结合系统函数与子查询

SQL Server 中用 `STRING_SPLIT` 配合子查询拆分逗号分隔字符串

SQL Server 2016+ 原生支持 STRING_SPLIT，但它返回的是表值函数结果，不能直接用于 WHERE 或 JOIN 的右侧——必须用子查询或 APPLY 包一层。常见错误是写成 WHERE col IN (SELECT value FROM STRING_SPLIT('a,b,c', ','))，这在语法上合法，但若外部查询有参数（比如来自另一张表的字段），就会报错“无法绑定多部分标识符”。

正确做法是把 STRING_SPLIT 放进 CROSS APPLY：

SELECT t.id, t.name, s.value AS tag
FROM products t
CROSS APPLY STRING_SPLIT(t.tags, ',') s
WHERE TRIM(s.value) = 'urgent';

STRING_SPLIT 不保证返回顺序，如需按原始位置排序，得额外加 ORDER BY 并依赖 SQL Server 2022+ 的 ordinal 列（启用 enable_ordinal = 1 参数）
TRIM() 必须显式调用，否则空格会导致匹配失败（如 ' urgent ' ≠ 'urgent'）
该函数不支持多字符分隔符，若用 ; 或 | 拆分，仍可用，但遇到 ;; 会产生空行，需加 WHERE s.value != ''

MySQL 8.0+ 用 `JSON_TABLE` 模拟字符串拆分 + 子查询关联

MySQL 没有内置字符串拆分函数，但 8.0+ 的 JSON_TABLE 可以把逗号分隔字符串转成 JSON 数组再展开。难点在于：输入必须是合法 JSON 格式，所以得先用 REPLACE 和拼接把 'a,b,c' 变成 '["a","b","c"]'，再喂给 JSON_TABLE。

典型写法：

SELECT t.id, jt.tag
FROM orders t
CROSS JOIN JSON_TABLE(
  CONCAT('["', REPLACE(t.category_path, ',', '","'), '"]'),
  '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')
) AS jt
WHERE jt.tag = 'electronics';

如果 t.category_path 含双引号或反斜杠，CONCAT 会破坏 JSON 结构，必须先用 REPLACE 转义（如 REPLACE(REPLACE(t.category_path, '"', '\"'), '\', '\\')）
JSON_TABLE 是横向展开，一行变多行，和 CROSS APPLY 行为一致；但性能比原生函数差，大数据量时建议提前物化到临时表
MySQL 5.7 及更早版本只能靠递归 CTE 或存储过程模拟，不可靠且难以调试

PostgreSQL 中用 `string_to_array` + `UNNEST` 实现带条件的嵌套过滤

PostgreSQL 的 string_to_array 返回数组，配合 UNNEST 展开后，可直接在子查询中参与 JOIN 或 EXISTS。但注意：UNNEST 默认不保留空元素，且无法直接关联原表字段做条件推导。

安全写法（避免空数组报错）：

SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(string_to_array(u.roles, ',')) r(role_name)
  WHERE TRIM(r.role_name) IN ('admin', 'editor')
);

如果 u.roles 为空字符串或 NULL，string_to_array 返回空数组或 NULL，UNNEST 会跳过整行——这是预期行为，不用额外判空
想同时获取拆分后的序号？用 WITH ORDINALITY：UNNEST(string_to_array(...)) WITH ORDINALITY AS r(role_name, idx)
正则拆分更灵活（如按空白、分号或多个逗号），用 regexp_split_to_table(u.tags, '[,s;]+')，但性能略低

跨数据库兼容写法：用关联子查询 + 自定义拆分逻辑（慎用）

当必须兼容老版本（如 SQL Server 2014、MySQL 5.6）且无法改库时，唯一办法是写标量函数或内联表值函数模拟拆分。但这类函数极易成为性能瓶颈——每次调用都触发一次循环，且无法利用索引。

例如 SQL Server 中一个危险的标量函数：

CREATE FUNCTION dbo.SplitString(@str NVARCHAR(MAX), @sep CHAR(1))
RETURNS @result TABLE (value NVARCHAR(4000))
AS BEGIN
  DECLARE @i INT = 1, @j INT;
  WHILE @i

千万别在 WHERE 子句里调用它，比如 WHERE 'x' IN (SELECT value FROM dbo.SplitString(col, ',')) —— 这会让优化器放弃所有索引，全表扫描
替代方案：把拆分结果提前存到关联表，用标准 JOIN；或者用应用层拆分后生成 IN 列表（注意参数个数限制）
几乎所有“通用拆分函数”在大数据场景下都会拖垮查询，这是最常被忽略的代价