假设我有个Table C,内容如下
date name value
20070701 AA 100
20070701 BB 100
20070702 AA 200
20070702 BB 200
20070703 AA 300
20070703 BB 300
我现在想把它显示成
date name value
20070701 AABB 200
20070701 AABB 400
20070702 AABB 600
这个PLSQL应该怎么写呀,谢谢!
怎样用函数的方法解决呢?
date name value
20070701 AA 100
20070701 BB 100
20070702 AA 200
20070702 BB 200
20070703 AA 300
20070703 BB 300
我现在想把它显示成
date name value
20070701 AABB 200
20070701 AABB 400
20070702 AABB 600
这个PLSQL应该怎么写呀,谢谢!
怎样用函数的方法解决呢?
解决方案 »
- RAC+ASM+DB10gR2+Redhat5.5 装DB软件时,远程节点老是重启
- 查询表中Route这个字段的整列中数据是否有符合where Route = '路由2';前面怎么写?
- 如何查看oracle表空间建立时的语句?
- 求SQL查询大数据优化
- rac环境DBID跟SID怎么回事,不了解,问下,谢谢了!
- 专家救命!在线等待!
- “ORA-06553:PLS-213:STANDARD 包不可访问 ORA-00604递归SQL层出现错误”怎么解决?以前的贴子上的方法解决不了。
- exp问题
- [建议]大家收集所有关于Oracle的电子版,方便大家使用。众人捡材火力越旺!
- oracle 表数据是会更新的,查询的时候又必须分页查询,怎么分页查询办?
- alter database 添加一个数据文件后能否回滚?
- solaris下恢复oracle
create table tablec(cdate varchar2(100), cname varchar2(100), cvalue int );
insert into tablec
select '20070701','AA',100 from dual union all
select '20070701','BB',100 from dual union all
select '20070702','AA',200 from dual union all
select '20070702','BB',200 from dual union all
select '20070703','AA',300 from dual union all
select '20070703','BB',300 from dual;
--建立函数
create or replace function sum_string(v_sql varchar2)
return varchar2
as
type cur_alldata is ref cursor;
l_alldata cur_alldata;
v_row varchar2(99);
v_sum varchar2(3999);
begin
open l_alldata for v_sql;
loop
fetch l_alldata into v_row;
exit when l_alldata%notfound;
v_sum := v_sum||v_row;
end loop;
close l_alldata;
return v_sum;
end;
--执行查询
select cdate,
sum_string('select cname from tablec where cdate='''||cdate||''' group by cdate,cname') cname,
sum(cvalue) cvalue
from tablec
group by cdate;
--测试结果
20070701 AABB 200
20070702 AABB 400
20070703 AABB 600
select ttt.cdate, ltrim(MAX(sys_connect_by_path(cname, ',')), ','),sum(cvalue) from
(select t.*, (row_number() over (partition by t.cdate ORDER BY t.cname )) NumID from tablec t) ttt
START WITH NumID = 1
CONNECT BY NumID-1 = PRIOR NumID
group by ttt.cdate