多值字段两表关联SQL查询：获取教师教授的全部科目

我来帮你搞定这个问题～你遇到的核心问题是User_Option_Experience表里的teaching_in字段存的是逗号分隔的字符串，直接和Teaching_in表关联时，数据库没办法识别字符串里的多个科目ID，所以只会返回单条结果。解决思路是先把这个逗号分隔的字段拆分成独立的行，再和科目表关联查询。

下面分几种常见数据库给出具体的查询语句：

MySQL 8.0+ 版本 #

用递归CTE（公共表表达式）来拆分字符串：

WITH RECURSIVE split_teaching AS (
    SELECT 
        user_id,
        SUBSTRING_INDEX(teaching_in, ',', 1) AS teach_id,
        SUBSTRING(teaching_in, LOCATE(',', teaching_in) + 1) AS remaining
    FROM User_Option_Experience
    WHERE user_id = 111 -- 替换成你要查询的用户ID
    UNION ALL
    SELECT 
        user_id,
        SUBSTRING_INDEX(remaining, ',', 1) AS teach_id,
        SUBSTRING(remaining, LOCATE(',', remaining) + 1) AS remaining
    FROM split_teaching
    WHERE remaining != ''
)
SELECT t.name AS subject_name
FROM split_teaching s
JOIN Teaching_in t ON s.teach_id = t.IdTeaching;

SQL Server 2016+ 版本 #

用内置的STRING_SPLIT函数拆分字符串，写法更简洁：

SELECT t.name AS subject_name
FROM User_Option_Experience u
CROSS APPLY STRING_SPLIT(u.teaching_in, ',') s
JOIN Teaching_in t ON s.value = t.IdTeaching
WHERE u.user_id = 111; -- 替换成目标用户ID

PostgreSQL 版本 #

结合string_to_array和unnest函数来拆分：

SELECT t.name AS subject_name
FROM User_Option_Experience u
CROSS JOIN unnest(string_to_array(u.teaching_in, ',')) AS s(teach_id)
JOIN Teaching_in t ON s.teach_id::int = t.IdTeaching
WHERE u.user_id = 111; -- 替换成目标用户ID

额外建议 #

如果这个业务是长期使用的，我强烈建议你修改表结构，把User_Option_Experience里的teaching_in字段去掉，换成一张用户-科目关联表（比如叫User_Teaching_Relation），字段包含user_id和IdTeaching，每个用户教授的科目单独存一行。这样不仅查询更高效，后续维护和扩展也会更方便，完全符合数据库设计的范式要求。

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