SQL 分隔数据转换为多值IN列表

当我们在进行 SQL 查询时，有时候需要将一个字段中的多个值以 IN 列表的方式传递给查询语句中的 IN 关键字。这时候，我们需要将该字段中的每个元素进行分割然后组成一个 IN 列表，这就是分隔数据转换为多值 IN 列表的操作。下面我们将介绍两个实例，分别用 MySQL 和 PostgreSQL 实现。

MySQL 中的分隔数据转换为多值 IN 列表

假设我们有一个表名为 students，其中一个字段 courses 记录了学生所选的多个课程，这些课程被逗号 , 分隔。现在我们需要查询选了课程为 Java 和 Python 的学生信息。

首先，我们需要将 courses 字段中的数据分隔开来。在 MySQL 中，我们可以利用 SUBSTRING_INDEX() 函数对字符串进行分割：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(courses, ',', n), ',', -1) AS course
FROM students
CROSS JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) m
WHERE course IN ('Java', 'Python');

上述查询中，我们使用了 CROSS JOIN 来生成一个 1 到 4 的连续整数序列，通过 SUBSTRING_INDEX() 函数提取了每个学生的每个课程。最后，我们可以将所有选了 Java 和 Python 课程的学生信息进行聚合，例如：

SELECT *
FROM students
WHERE FIND_IN_SET('Java', courses) AND FIND_IN_SET('Python', courses);

上述查询中，我们使用了 FIND_IN_SET() 函数来查询选了 Java 和 Python 的学生信息。

PostgreSQL 中的分隔数据转换为多值 IN 列表

与 MySQL 不同，PostgreSQL 并没有内置的 SUBSTRING_INDEX() 函数，但它提供了 string_to_array() 函数来将字符串转换为数组，并且支持任意字符作为分隔符。

假设我们有一个表名为 students，其中一个字段 courses 记录了学生所选的多个课程，这些课程被逗号 , 分隔。现在我们需要查询选了课程为 Java 和 Python 的学生信息。

首先，我们需要将 courses 字段转换为数组：

SELECT unnest(string_to_array(courses, ',')) AS course
FROM students

上述查询中，unnest() 函数用于展开数组，并将数组中的每个元素作为一行返回。

接下来，我们可以将该查询的结果作为子查询，并用 HAVING 子句进行筛选：

SELECT *
FROM students
WHERE id IN (
  SELECT id
  FROM (
    SELECT id, unnest(string_to_array(courses, ',')) AS course
    FROM students
  ) AS courses
  WHERE course IN ('Java', 'Python')
  GROUP BY id
  HAVING COUNT(*) = 2
);

上述查询中，我们先将 courses 查询的结果作为子查询，然后使用 WHERE 子句筛选出选了 Java 和 Python 课程的学生，最后使用 GROUP BY 和 HAVING 子句确保每个学生只选了 Java 和 Python 两门课。