表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怎么写效率高啊? 能实现吗?
+----+------------+----------+
| 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怎么写效率高啊? 能实现吗?
解决方案 »
- Delphi+MySQL执行查询报Commands out of sync; you can't run this command now
- 自己整合的lamp中mysql查询时间 比 xampp中的mysql 慢了一倍左右
- 各位好心帮我看看,请教mysql4.1 排序问题 百分求助
- 用事务删除了一条数据,打开表看数据还在
- Delphi中如何调用MYSQL的存储过程?
- 如何使字符"100"大于"99",等待中!!!
- mysql中较大的数据如何存储的问题
- 寻一款free的数据库中间件
- winmysqladmin请教.
- 考勤的SQL语句问题
- jdbc+mysql3.23中文乱码问题
- 问个存储过程怎么写
from table_a
from table_a
group by subject_id;
+------+------------+----------+
| 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>
select subject_id,group_concat(DISTINCT audio_id)
from t_table_a
group by subject_id
order by subject_id;