在一个表中,如果对于A列出现重复的值,就把A列中出现重复的值对应的B列的值合并,请问怎么编写sql语句来实现?举个例子,有如下表:
studentID course score
001 math 82
002 english 68
002 chinese 92
003 physics 100
004 bioloy 75
005 physics 93
004 chinese 86
004 english 93执行sql语句后,能实现以下的查询结果
studentID course score
001 math 82
002 english,chinese 68,92
003 physics 100
004 bioloy,chinese,english 75,86,93
005 physics 93请问怎么编写sql语句?
studentID course score
001 math 82
002 english 68
002 chinese 92
003 physics 100
004 bioloy 75
005 physics 93
004 chinese 86
004 english 93执行sql语句后,能实现以下的查询结果
studentID course score
001 math 82
002 english,chinese 68,92
003 physics 100
004 bioloy,chinese,english 75,86,93
005 physics 93请问怎么编写sql语句?
解决方案 »
- 请哪位给一个空白电脑上安装Red Hat Enterprise Linux 5的详细教程,感激不尽~
- 一个时间格式的小问题
- 关于oracle与ibatis结合的问题?
- 企业管理器密码错误
- 如何删除两个表中的重复数据?
- oracle9i导出的dmp文件可以导入到oracle817吗?
- linux下oracle9204的连接问题,在linux服务端连不数据库,但是在客户端通过命令行(cmd>>sqlplus /nolog)可以连上, 问题解决马上结账,绝
- 视图生成成功,但是查看视图的数据时报错:无法在没有键值保存表的连接视图中选择rowid
- 关于listerner的问题?请进
- 请教一个关于存储过程调用的问题
- 请问ORACLE数据库如何设置函数的默认值和自增值的?????
- 求sql!!!
insert into tb(studentID,course,score) values('002', 'english', 68);
insert into tb(studentID,course,score) values('002', 'chinese', 92);
insert into tb(studentID,course,score) values('003', 'physics', 100);
insert into tb(studentID,course,score) values('004', 'bioloy', 75);
insert into tb(studentID,course,score) values('005', 'physics', 93);
insert into tb(studentID,course,score) values('004', 'chinese', 86);
insert into tb(studentID,course,score) values('004', 'english', 93);col course for a45;
col score for a45;
select studentID, wm_concat(course) as course, wm_concat(to_char(score)) as score
from (select studentID, course, score from tb order by studentID, score) t
group by t.studentID;
SQL> col score for a45;
SQL> select studentID, wm_concat(course) as course, wm_concat(to_char(score)) as score
2 from (select studentID, course, score from tb order by studentID, score) t
3 group by t.studentID;\
4
SQL> col course for a45;
SQL> col score for a45;
SQL> select studentID, wm_concat(course) as course, wm_concat(to_char(score)) as score
2 from (select studentID, course, score from tb order by studentID, score) t
3 group by t.studentID;STUDENTID COURSE SCORE
-------------------- --------------------------------------------- ---------------------------------------------
001 math 82
002 english,chinese 68,92
003 physics 100
004 bioloy,chinese,english 75,86,93
005 physics 93
那个函数是怎么定义的?
如果楼主还在问这样的问题的话,建议您Oracle 从零学起!
SET @FmtStr=''
SELECT @FmtStr=@FmtStr+','+ course FROM tb WHERE studentID =@studentID
RETURN STUFF(@FmtStr,1,1,'')