我建个表 t,字段为key,name
数据有:1,a;
1,b;
2,a;
2,b;
2,c;
我用:select t.key,wmsys.wm_concat(t.name) from t order by t.key
结果会成为:1 a,b;
2 c,a,b;
我想问下有没有方法使其第二行变为: 2 a,b,c ,也就是能排下序
数据有:1,a;
1,b;
2,a;
2,b;
2,c;
我用:select t.key,wmsys.wm_concat(t.name) from t order by t.key
结果会成为:1 a,b;
2 c,a,b;
我想问下有没有方法使其第二行变为: 2 a,b,c ,也就是能排下序
解决方案 »
- Oracle倒入dmp文件在dos下倒入,求大神!!
- 求教一个查询SQL
- oracle中有没有判断某一字符串为正确的日期型数据的函数
- 如何用存储过程向一个表中插入年历!
- !!!Solaris上出现ora-01461问题
- ORA-01017: invalid username/password; logon denied
- 如何启动服务:Oracle Management Server?
- 可是使用odac里面的ToraQuery组件调用存储过程,并返回游标吗?比如select语句的结果怎么返回给ToraQuery?
- 一个简单的问题,我想知道究竟存在什么表和多少个表,SQL语句怎么写啊。
- 想调查一下,你们的计算机内存有多少?
- 用存储过程来实现 数据迁移 的问题
- oracle添加另外的表中的数据
select t.key,wmsys.wm_concat(t.name)
from (select * from t order by key) t
group by t.key
select key,wmsys.wm_concat(name) from (
select t.key,t.name from t order by t.key,t.name
) group by key
--wm_concat函数其实也是一个开窗函数,因此直接套用分析函数就可以了
select t.key,wmsys.wm_concat(t.name)over(partition by t.key order by t.name) from t order by t.key
FROM (SELECT t.key,
wmsys.wm_concat(t.NAME) over(PARTITION BY t.key ORDER BY t.NAME) NAME
FROM t)
GROUP BY key;
paddy的外面再套一层
minitoy正解~~~with tab as
(select 1 key, 'a' name from dual union all
select 1,'b' from dual union all
select 2,'a' from dual union all
select 2,'b' from dual union all select 2,'c' from dual)SELECT key, MAX(NAME) NAME
FROM (SELECT t.key,
wmsys.wm_concat(t.NAME) over(PARTITION BY t.key ORDER BY t.NAME) NAME
FROM tab t)
GROUP BY key;
------------------------------------------------------------------
1 a,b
2 a,b,c