我有这样一表tt
c1 c2
1 22
1 33
1 44
2 55
2 66
现要转成
c1 c2
1 99(即22+33+44)
2 121(即55+66)
谢谢各位大侠!!1
小弟现在转成了
c1 c2
1 223344
2 5566create or replace function sys.get11(tmp number)
return number
IS
col_c2 number;
begin
for cur in (select c2 from tt where c1=tmp) loop
col_c2 :=col_c2||cur.c2;end loop;
col_c2:=rtrim(col_c2,1);
return col_c2;
end;sql>select distinct c1,get11(c1) c3 from tt order by c1
c1 c2
1 22
1 33
1 44
2 55
2 66
现要转成
c1 c2
1 99(即22+33+44)
2 121(即55+66)
谢谢各位大侠!!1
小弟现在转成了
c1 c2
1 223344
2 5566create or replace function sys.get11(tmp number)
return number
IS
col_c2 number;
begin
for cur in (select c2 from tt where c1=tmp) loop
col_c2 :=col_c2||cur.c2;end loop;
col_c2:=rtrim(col_c2,1);
return col_c2;
end;sql>select distinct c1,get11(c1) c3 from tt order by c1
解决方案 »
- 请教以下语句如何改造成查询不重复记录
- web游戏数据库设计
- 请问大家如何对一个大表进行分批update操作
- orcale 分组查询问题
- 问什么setAutoCommit没有作用
- oracle中字符型转换成整型用什么函数?急!!在线等待
- insert into 问题? 简单..
- 帮帮忙
- 高分请问:如何将 Ms Sql Server 7.0 中的数据库移植到 Oracle 8i 上呢?
- 大家说说,现在学哪种数据库比较吃香。
- oracle的逻辑判断(在表不存在期望的数据的判断)
- ORA-06502: PL/SQL: 数字或值错误,ORA-06512: 在"HMNEWDB.FLOATTOBITARRAY", line 15
from tt
group by c1
你想要的结果是这个样子吗?
from ctest
group by c1
有表:
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD14 rows selected.
想输出为:
DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 除了使用聚集函数或者存储过程之外,9i中可以:
SQL> SELECT deptno
2 , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
4 FROM ( SELECT deptno
5 , ename
6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
8 FROM emp )
9 GROUP BY deptno
10 CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
11 START WITH curr = 1;
DEPTNO----------CONCATENATED----------------------------------------------------------------------------------------------------
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD10CLARK,KING,MILLER 20ADAMS,FORD,JONES,SCOTT,SMITH 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
from tt
group by c1
就可以了啊
http://blog.csdn.net/zhpsam109/archive/2007/12/04/1917011.aspx
group by c1