select to_char(wmsys.wm_concat(DISTINCT name)) from T
解决方案 »
- 在线等 急求 sum 函数使用
- 请教关于分组数据字段拼接的SQL语句问题
- 求教一个SQL语句写法的问题
- the network adapter could not establish the connection
- 各位老师我有一个巨简单的问题,主键自增的问题
- 求一View…………
- orcal 默认排序相关问题,谢谢(急)
- sqlldr userid=nmc/nmc control=c;\input.txt 为什么无法执行? 在线等
- oracle的异常处理问题(.net 应用)
- 偶发现一个很好的Oracle技术论坛,大家有什么Oracle疑问快去那个论坛问,那里有很多技术高超的版主为你及时解决!!!
- 求Oracle创建视图相关语句
- toad 链接自己建的Oracle数据库无响应不报错
(select 10 xh, 'aa' name
from dual
union all
select 20 xh, 'bb' name
from dual
union all
select 20 xh, 'cc' name
from dual
union all
select 30 xh, 'cc' name
from dual
union all
select 40 xh, 'cc' name
from dual
union all
select 50 xh, 'cc' name
from dual
union all
select 60 xh, 'dd' name
from dual
union all
select 70 xh, 'ee' name
from dual
union all
select 80 xh, 'ee' name
from dual)
select max(str)
from (select to_char(wm_concat(name) over(order by name)) str
from (select distinct name from t));
xh name
10 aa
20 bb
30 bb
40 aa
50 cc
60 cc
需要结果是 aa,bb,aa,cc,怎么改呢?
select to_char(wmsys.wm_concat(name)) from T
以上语句,distinct去掉即可
select max(str)
from (
select to_char(wm_concat(name) over(order by xh)) str
from (select xh,name,lag(name,1,' ')over(order by xh) pre from t )
where pre<>name
)
不对的,去掉DISTINCT出来是这个,不是我要的结果是aa,bb,aa,cc,dd
(select 10 xh, 'aa' name
from dual
union all
select 20 xh, 'bb' name
from dual
union all
select 20 xh, 'bb' name
from dual
union all
select 30 xh, 'aa' name
from dual
union all
select 40 xh, 'cc' name
from dual
union all
select 50 xh, 'cc' name
from dual
union all
select 60 xh, 'dd' name
from dual
union all
select 70 xh, 'ee' name
from dual
union all
select 80 xh, 'ee' name
from dual)
select max(str)
from (
select to_char(wm_concat(name) over(order by xh)) str
from (select xh,name,lag(name,1,' ')over(order by xh) pre from t )
where pre<>name
)结果
MAX(STR)
---------------------aa,bb,aa,cc,dd,ee