如何在SQL中拆分不定数量的逗号分隔IP列并关联单IP列？

针对逗号分隔多IP列的拆分与单IP列的一对多关联需求，以下是不同数据库系统下的可行方案：

1. PostgreSQL #

利用string_to_array将字符串转数组，再通过unnest展开数组实现关联：

SELECT 
    "Single IP col",
    unnest(string_to_array(trim("Multiple IP col"), ',')) AS "Multiple IP col"
FROM your_table;

注：若多IP列末尾存在多余逗号，可先用trim(trailing ',' from "Multiple IP col")清理后再拆分。

2. MySQL 8.0+ #

借助JSON_TABLE将逗号分隔字符串转为JSON数组后展开：

SELECT 
    `Single IP col`,
    j.ip AS `Multiple IP col`
FROM your_table
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(`Multiple IP col`, ',', '","'), '"]'),
    '$[*]' COLUMNS (ip VARCHAR(15) PATH '$')
) j;

若使用MySQL 5.x版本，可用递归CTE实现：

WITH RECURSIVE cte AS (
    SELECT 
        `Single IP col`,
        `Multiple IP col` AS ip_list,
        SUBSTRING_INDEX(`Multiple IP col`, ',', 1) AS ip,
        SUBSTRING(`Multiple IP col`, LOCATE(',', `Multiple IP col`) + 1) AS remaining
    FROM your_table
    WHERE `Multiple IP col` IS NOT NULL AND `Multiple IP col` != ''
    UNION ALL
    SELECT 
        `Single IP col`,
        remaining,
        SUBSTRING_INDEX(remaining, ',', 1),
        SUBSTRING(remaining, LOCATE(',', remaining) + 1)
    FROM cte
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT `Single IP col`, ip AS `Multiple IP col` FROM cte;

3. SQL Server 2016+ #

使用内置STRING_SPLIT函数直接拆分并关联：

SELECT 
    [Single IP col],
    value AS [Multiple IP col]
FROM your_table
CROSS APPLY STRING_SPLIT([Multiple IP col], ',');

4. 通用递归CTE方案（兼容多数数据库） #

若数据库无内置拆分函数，递归CTE是通用解决方案：

WITH RECURSIVE split_ip AS (
    SELECT 
        "Single IP col",
        TRIM(SUBSTRING("Multiple IP col", 1, CHARINDEX(',', "Multiple IP col") - 1)) AS ip,
        TRIM(SUBSTRING("Multiple IP col", CHARINDEX(',', "Multiple IP col") + 1, LENGTH("Multiple IP col"))) AS remaining_ip
    FROM your_table
    WHERE CHARINDEX(',', "Multiple IP col") > 0
    UNION ALL
    SELECT 
        "Single IP col",
        CASE WHEN CHARINDEX(',', remaining_ip) > 0 THEN TRIM(SUBSTRING(remaining_ip, 1, CHARINDEX(',', remaining_ip) - 1)) ELSE TRIM(remaining_ip) END AS ip,
        CASE WHEN CHARINDEX(',', remaining_ip) > 0 THEN TRIM(SUBSTRING(remaining_ip, CHARINDEX(',', remaining_ip) + 1, LENGTH(remaining_ip))) ELSE '' END AS remaining_ip
    FROM split_ip
    WHERE remaining_ip != ''
    UNION ALL
    SELECT 
        "Single IP col",
        TRIM("Multiple IP col") AS ip,
        '' AS remaining_ip
    FROM your_table
    WHERE CHARINDEX(',', "Multiple IP col") = 0 AND "Multiple IP col" != ''
)
SELECT "Single IP col", ip AS "Multiple IP col" FROM split_ip;

内容的提问来源于stack exchange，提问作者Czar19