例表:
no name jiner you
01 ss 10 麦子
02 dd 11 萝卜
03 cc 9 大豆
01 ss 10 白菜
求表no name jiner you
01 ss 10 麦子,白菜
02 dd 11 萝卜
03 cc 9 大豆看看大家咋么实现,另外在oracle和SQL中是否一样实现,谢谢
no name jiner you
01 ss 10 麦子
02 dd 11 萝卜
03 cc 9 大豆
01 ss 10 白菜
求表no name jiner you
01 ss 10 麦子,白菜
02 dd 11 萝卜
03 cc 9 大豆看看大家咋么实现,另外在oracle和SQL中是否一样实现,谢谢
解决方案 »
- ORA-01019无法在用户方分配内存问题?
- Java
- ASP.NET调用Oracle存储过程问题
- System.ArgumentException: 不支持关键字: “provider”
- 请问,Oracle10g的integer型支持的最大数值是多少?
- 真的没有人知道么(关于EXP和IMP的裁剪)
- 导入数据库时出现警告,请问到底是什么原因呢?
- oracle中如何插入图片到BLOB中
- 急!如何在sql语句中对某个字段进行位或操作?
- 一个sql查询怪异的情况,求帮忙分析下为什么,想不通
- 装win2k3系统的服务器重启后oracle实例启动不了
- oracle10g-XE (快捷版)的client能否链接到oracle10g server(非快捷版的)数据库么?
select no,name,jiner,wm_concat(you) you from table;9i及以前的需要用sys_connect_by_path。
from tab
group by no,name,jiner
select no,name,jiner,wm_concat(you) you from table
group by no,name,jiner;
WITH tab AS(
select '01' no,'ss' name, 10 jiner,'麦子' you from dual union all
select '02' no,'dd' name, 11 jiner,'萝卜' you from dual union all
select '03' no,'cc' name, 9 jiner,'大豆' you from dual union all
select '01' no,'ss' name, 10 jiner,'白菜' you from dual)
SELECT a.no, a.name, a.jiner, ltrim(MAX(sys_connect_by_path(you, ',')), ',') you
FROM (SELECT row_number() over(PARTITION BY a.no, a.name, a.jiner
ORDER BY a.no, a.name, a.jiner) rn,a.*
FROM tab a) a
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND
a.no = PRIOR a.no AND
a.name = PRIOR a.name AND
a.jiner = PRIOR a.jiner
GROUP BY a.no, a.name, a.jiner
from (select row_number() over(partition by no order by no)rn,no,name,jiner,you from tab)
start with rn=1
connect by prior no=no
and prior name=name
and prior jiner=jiner
and prior rn+1=rn
group by no,name,jiner
order by no