如何选择仅重复一个字段且所有其他字段值重复的记录? [英] How to select records with duplicate just one field and all other field value?

问题描述

我的表名= schedule

 --------------------------------------------
| course_id | room |   day  |     time     |
--------------------------------------------
|     2     |  401 |saturday| 9:00 - 13:00 |
--------------------------------------------
|     3     |  401 | sunday | 9:00 - 13:00 |
--------------------------------------------
|     2     |  402 | monday | 9:00 - 13:00 |
--------------------------------------------
|     3     |  403 | tuesday| 14:00 - 17:00|
--------------------------------------------
|     4     |  401 | tuesday| 9:00 - 13:00 |
--------------------------------------------
|     2     |  402 |wednesday|14:00 - 17:00|
--------------------------------------------

另一个表名=课程

----------------------------------
| id | course_code | course_name |
----------------------------------
| 2  |  cse        | cse         | 
------------------------
| 3  |  eee        | eee         | 
----------------------
| 4  |  ct         | ct          | 
-----------------------

现在如何获得这样的输出.

Now how to get output like this..

----------------------------------
| course code | course name | info |
----------------------------------
|cse          |  cse        |401,satueday-9:00-13:00; 402,monday-9:00-13:00; 402,wednesday-14:00-17:00|
------------------------
|    eee      | eee         | 401,sunday-9:00-13:00;403,tuesday-14:00-17:00 |
------------------------
|    ct       |  ct         | 401,tuesday-9:00-13:00|
-----------------------

推荐答案

您可以使用group_concat

You can use group_concat

select c.course_code,
    c.course_name,
    group_concat(concat(s.room, ',', s.day, '-', s.time) separator ';')
from course c
join schedule s on s.course_id = c.id
group by c.course_code,
    c.course_name;

这篇关于如何选择仅重复一个字段且所有其他字段值重复的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！