MYSQL 数据对应问题

Question

SELECT id,course from student

SELECT id,tags from course

course表是对应关系，id与 student表中的course对应。

student的id为索引，需要这样的查询结果：

| id | course  | tags  |
| ------ | ------ | ------ |
| id | 30,31,32,33,34| 31,32,33,34,35 |
| id | 30,31,32,33,34| 31,32,33,34,35 |

Answer 1

1、MySQL 5.7 测试通过

SELECT s.id, s.course, group_concat( c.target ORDER BY c.target ) target
  FROM ( SELECT 1 AS id, '30,31,32,33,34' AS course UNION SELECT 2, '28,29,30,31,32' UNION SELECT 3, '32,33,34,38' ) s LEFT JOIN 
       ( SELECT 28 AS id, 29 AS target UNION SELECT 29, 30 UNION SELECT 30,31 UNION SELECT 31,32 UNION SELECT 32,33 UNION SELECT 33,34 UNION SELECT 34,35 UNION SELECT 35,36 UNION SELECT 37,38 UNION SELECT 38,39 ) c
    ON concat( ',', s.course, ',' ) LIKE concat( '%,', c.id, ',%' )
 GROUP BY s.id, s.course
 ORDER BY s.id, s.course

输出结果：