sql - 如何使用第一个表中的逗号分隔值从另一个表中选择行？

Question

有没有办法使用第一个表中的逗号分隔值从另一个表中选择行？

表格1。faculty

学科
101, 102
104
103, 105

表 2。subject

代码	学科
101	科目一
102	科目二
103	主题 3
104	科目四
105	主题 5

预期输出：

学科	学科
101, 102	科目一、科目二
104	科目四
103, 105	科目三、科目五

我已经尝试过了：

SELECT faculty.subject, subject_offered.code, subject_offered.subject 
       FROM faculty 
       LEFT JOIN subject_offered 
       ON subject_offered.code 
       IN (faculty.subject)

但是faculty具有多个值（逗号分隔）的行在代码和表中的主题列中显示 NULL subject。

我的输出

标签： sql

Answer 1

更新

请参阅@Akina 的答案以获得完整正确和更清洁的解决方案。

虽然这是一个非常糟糕的桌子设计，但仍有办法解决这个问题。

解决方案

SELECT faculty_subject AS faculty_subject_codes, GROUP_CONCAT(subject ORDER BY code ASC) AS subjects
FROM (
  SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%')
) fa GROUP BY faculty_subject;

给

Faculty_subject_codes	科目
101,102	科目一、科目二
103,105	科目三、科目五
104	科目四

查看我的小提琴以了解您的问题

解释

由于faculty.subject是逗号分隔的值，因此您不能通过 进行常规连接subject.code，因此解决方法是改用JOINwithLIKE子句。

SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%');

教师主题	代码	学科
101,102	101	科目一
101,102	102	科目二
103,105	103	主题 3
104	104	科目四
103,105	105	主题 5

现在我们有多个具有相同 Faculty_subject 的行，例如代码 103 和 105。接下来是将这些重复的行合并到一个条目中，我们将使用GROUP CONCAT语句来执行此操作，GROUP BY并且faculty_subject

SELECT faculty_subject AS faculty_subject_codes, GROUP_CONCAT(subject ORDER BY code ASC) AS subjects
FROM (
  SELECT faculty.subject as faculty_subject, subject.*
FROM subject
JOIN faculty 
ON faculty.subject LIKE CONCAT('%', subject.code, '%')
) fa GROUP BY faculty_subject;