我有这样一表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
解决方案 »
- Oracle 10.2.0.1 如何安装 ProC ?????
- 简单SQL,谢谢帮忙!
- 求: 如何在oracle中实现下列功能的sql句子?
- 新手上路!在xp下安装oracle9i的问题!
- ORACLE数据库字符集问题,紧急
- sql排序问题?
- 权限的问题
- 关于大量数据insert的问题
- ORACLE新手--怎样把MYSQL中的结构与数据导入ORACLE中?
- PLSQL中如何直接用SQL语句直接连接远程ORACLE数据库读取数据
- 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