不用存储过程,只用一个SQL,怎么把一列的所有的值连在一起返回
例如
select col_a from testcol_a
~~~~~~~
1
2
3
4
5现在要返回这样的值
select *** result from ***result
~~~~~~~
1,2,3,4,5
例如
select col_a from testcol_a
~~~~~~~
1
2
3
4
5现在要返回这样的值
select *** result from ***result
~~~~~~~
1,2,3,4,5
解决方案 »
- 写一条SQL语句如何判断一张表(scot)内某只股票在一段时间内的持续下跌情况,表中字段有 股票编号(No1),股票名称(name1),时间(date1),收盘
- 请问oracle存储过程中,循环的时候有没有像JAVA那种continue 功能
- Oracle临时表问题
- 请问VS2010(C#)连接Oracle11g的字符串是什么?
- 优化如下的SQL语句
- shmmax 的这句话要怎么理解!
- [急!!!]高分求SQL语句,内详,高手请进,谢谢!
- 在oracle中,有没有类似SQL中的通配符?
- 为什么会出错?是版本太低吗?
- 使用SQL语句隐藏查询后的某一列,求大神教教
- 非常简单的问题,各位大侠救命啊
- 奇怪的SQL???
dinya2003(OK):
只有一列,怎么进行行列转换?
SQL > select job,ename from emp;
JOB ENAME
--------- ----------
CLERK SMITH
SALESMAN ALLEN
SALESMAN WARD
MANAGER JONES
SALESMAN MARTIN
MANAGER BLAKE
MANAGER CLARK
ANALYST SCOTT
PRESIDENT KING
SALESMAN TURNER
CLERK ADAMS
JOB ENAME
--------- ----------
CLERK JAMES
ANALYST FORD
CLERK MILLER
已选择14行。
SQL > create or replace function f_ename(jobno in varchar2)
2 return varchar2 is
3
4 str_return varchar2(200);
5 cursor c_ename(v_jobno varchar2) is select ename from emp where job=v_jobno;
6 begin
7 for str_temp in c_ename(jobno) loop
8 str_return:=str_return | |str_temp.ename;
9 end loop;
10 return str_return;
11 end;
12 /
函数已创建。
SQL > select f_ename(job) from (
2 select distinct job from emp);
F_ENAME(JOB)
---------------------------------------
SCOTT FORD
SMITH ADAMS JAMES MILLER
JONES BLAKE CLARK
KING
ALLEN WARD MARTIN TURNER
SQL >
加上job列:
SQL > select job,f_ename(job) from (
2 select distinct job from emp);
JOB F_ENAME(JOB)
-------------------------------------------------------
ANALYST SCOTT FORD
CLERK SMITH ADAMS JAMES MILLER
MANAGER JONES BLAKE CLARK
PRESIDENT KING
SALESMAN ALLEN WARD MARTIN TURNER
SQL> select * from test; MARK
-----
88
96
84
89SQL>
SQL> select ||','||2||','||3||','||4 result from (
2 select ,lead(,1,0)over(partition by rid order by ) 2,
3 lead(,2,0)over(partition by rid order by ) 3,
4 lead(,3,0)over(partition by rid order by ) 4
5 from ( select ,1 rid from test)) where rownum=1 ;RESULT
--------------------------------------------------------------------------------
84,88,89,96SQL>
我觉得光用一个SQL不能完全解决这个问题。
----------
1
2
3
5
7select ltrim(x, ',') from (
select x from (
select level lv, sys_connect_by_path(c1, ',') x from (select c1, rownum rn from test order by 1 desc)
connect by prior rn = rn - 1)
order by lv desc
)
where rownum = 1
/