这个sql该怎么写呢？

Question

表table_a 如下
+----+------------+----------+
| id | subject_id | audio_id |
+----+------------+----------+
| 16 |          4 | 4993     |
| 17 |          4 | 4994     |
| 18 |          4 | 4995     |
| 19 |          4 | 4996     |
| 20 |          4 | 3964     |
| 21 |          4 | 4977     |
| 22 |          5 | 4978     |
| 23 |          5 | 4979     |
| 24 |          5 | 4980     |
| 25 |          5 | 4981
 | 26 |          5 | 4982     |
| 27 |          5 | 4983     |
| 28 |          6 | 4984     |
| 29 |          6 | 4985     |
| 30 |          6 | 4986     |
+----+------------+----------+
 
现在想按照subject_id分组 得到类似如下的结果 subject_id | audio_id的链接串
------------+-------------------------+
          4 | 4993,4994,4995,4996
          5 | 4978，4979，4980，4981
请问   sql怎么写效率高啊？ 能实现吗？

Answer 1

select subject_id,group_concat(audio_id) as a from table group by subject_id

Answer 2

select subject_id,group_concat(audio_id order by audio_id asc separator ',') as a from table group by subject_id

Answer 3

用group_concat函数处理：select subject_id,group_concat(audio_id) as audio_id的链接串 
from table_a

Answer 4

select subject_id,group_concat(audio_id)
from table_a
group by subject_id;

Answer 5

mysql> select * from t_table_a;
+------+------------+----------+
| id   | subject_id | audio_id |
+------+------------+----------+
|   16 |          4 |     4993 |
|   17 |          4 |     4994 |
|   18 |          4 |     4995 |
|   19 |          4 |     4996 |
|   20 |          4 |     3964 |
|   21 |          4 |     4977 |
|   22 |          5 |     4978 |
|   23 |          5 |     4979 |
|   24 |          5 |     4980 |
|   25 |          5 |     4981 |
|   26 |          5 |     4982 |
|   27 |          5 |     4983 |
|   28 |          6 |     4984 |
|   29 |          6 |     4985 |
|   30 |          6 |     4986 |
+------+------------+----------+
15 rows in set (0.00 sec)mysql>
mysql> select subject_id,group_concat(audio_id)
    -> from t_table_a
    -> group by subject_id;
+------------+-------------------------------+
| subject_id | group_concat(audio_id)        |
+------------+-------------------------------+
|          4 | 4993,4994,4995,4996,3964,4977 |
|          5 | 4983,4982,4981,4980,4979,4978 |
|          6 | 4984,4985,4986                |
+------------+-------------------------------+
3 rows in set (0.05 sec)mysql>

Answer 6

应该还要考虑排序并剔除重复列，完整写法：
select subject_id,group_concat(DISTINCT audio_id)
from t_table_a
group by subject_id
order by subject_id;