with tmp as ( select '张三' as "姓名", '语文' as "课程", 70 as "分数" from dual union all select '张三' as "姓名", '数序' as "课程", 60 as "分数" from dual union all select '张三' as "姓名", '英语' as "课程", 90 as "分数" from dual union all select '李四' as "姓名", '历史' as "课程", 80 as "分数" from dual union all select '李四' as "姓名", '地理' as "课程", 60 as "分数" from dual union all select '周武' as "姓名", '生物' as "课程", 80 as "分数" from dual union all select '周武' as "姓名", '政治' as "课程", 70 as "分数" from dual ) select 姓名, wm_concat(课程 || ' ' || 分数) result from tmp group by 姓名;姓名 RESULT --------- -------------------------------------------------- 周武 生物 80,政治 70 张三 语文 70,数序 60,英语 90 李四 历史 80,地理 60
顶2 楼的 实在不行的话 CASE WHEN sum() 也可以弄出来
SQL>select 姓名,wm_concat(课程||' '||分数) 课程以及分数 from tb group by 姓名 SQL> / 姓名 课程以及分数 ---- ------------------------------ 李四 历史 80,地理 60 张三 语文 70,数序 60,英语 90 周武 生物 80,政治 70
with tmp as
(
select '张三' as "姓名", '语文' as "课程", 70 as "分数" from dual union all
select '张三' as "姓名", '数序' as "课程", 60 as "分数" from dual union all
select '张三' as "姓名", '英语' as "课程", 90 as "分数" from dual union all
select '李四' as "姓名", '历史' as "课程", 80 as "分数" from dual union all
select '李四' as "姓名", '地理' as "课程", 60 as "分数" from dual union all
select '周武' as "姓名", '生物' as "课程", 80 as "分数" from dual union all
select '周武' as "姓名", '政治' as "课程", 70 as "分数" from dual
)
select 姓名, wm_concat(课程 || ' ' || 分数) result
from tmp
group by 姓名;姓名 RESULT
--------- --------------------------------------------------
周武 生物 80,政治 70
张三 语文 70,数序 60,英语 90
李四 历史 80,地理 60
CASE WHEN sum() 也可以弄出来
SQL>select 姓名,wm_concat(课程||' '||分数) 课程以及分数 from tb group by 姓名
SQL> /
姓名 课程以及分数
---- ------------------------------
李四 历史 80,地理 60
张三 语文 70,数序 60,英语 90
周武 生物 80,政治 70