MySQL Select from 列使用 ^ 作为分隔符

Question

我的问题类似于 MySQL Split String and Select with results .目前我有 2 个表:

学生

uid | subject_id | name
1   | 1^2^3^4    | a
2   | 2^3^       | b
3   | 1          | c

主题

uid | subject_name
1   | math
2   | science
3   | languange
4   | sport

我期望的结果是:

uid | name    | subject_passed
1   | a       | math, science, languange, sport
2   | b       | science, languange
3   | c       | sport

我试过这个查询:

SELECT
student.uid,
student.name, 
group_concat(subject.subject_name) as subjects_passed 
from student 
join subject on find_in_set(subject.uid,student.subject_id ) > 0
group by student.uid

返回错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join subject on find_in_set(subject.uid,student.subject_id ) > 0 group' at line 7

我相信是因为FIND_IN_SET .根据文档，此函数需要 , 作为分隔符。我可以使用任何替代查询吗？

Answer 1

为什么不 REPLACE分隔符:

SELECT
    student.uid,
    student.name,
    GROUP_CONCAT(subject.subject_name) AS subjects_passed
FROM student
JOIN subject ON FIND_IN_SET(subject.uid, REPLACE(student.subject_id, '^', ',')) > 0
GROUP BY student.uid

SQLFiddle

---

如果您决定对表进行反规范化，那么创建联结表并生成数据是相当直接的:

-- Sample table structure
CREATE TABLE student_subject (
    student_id int NOT NULL,
    subject_id int NOT NULL,
    PRIMARY KEY (student_id, subject_id)
);

-- Sample query to denormalize student <-> subject relationship
SELECT
    student.uid AS student_id,
    subject.uid AS subject_id
FROM student
JOIN subject ON FIND_IN_SET(subject.uid, REPLACE(student.subject_id, '^', ',')) > 0

+------------+------------+
| student_id | subject_id |
+------------+------------+
|          1 |          1 |
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          2 |
|          2 |          3 |
|          3 |          1 |
+------------+------------+