emp:
id
name
depart,1 scott 1
2 tiger 1
3 manager 1比方说,select str_link(t.name) from emp t where t.depart = 1,
我要查出来的结果是scott,tiger,manager。搜了半天google,不知道怎么用关键字,看各位有么有遇到有这种需求的。
id
name
depart,1 scott 1
2 tiger 1
3 manager 1比方说,select str_link(t.name) from emp t where t.depart = 1,
我要查出来的结果是scott,tiger,manager。搜了半天google,不知道怎么用关键字,看各位有么有遇到有这种需求的。
STATIC FUNCTION ODCIAggregateInitialize (
sctx IN OUT strlink
)
RETURN NUMBER
IS
BEGIN
sctx := strlink(NULL);
RETURN ODCICONST.SUCCESS;
END; MEMBER FUNCTION ODCIAggregateIterate (
self IN OUT STRLINK,
value IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
IF self.str IS NULL THEN
self.str := value;
ELSE
self.str := self.str || ',' || value; END IF;
RETURN ODCICONST.SUCCESS;
END; MEMBER FUNCTION ODCIAggregateTerminate (
self IN strlink,
value OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
value := self.str;
RETURN ODCICONST.SUCCESS;
END; MEMBER FUNCTION ODCIAggregateMerge (
self IN OUT strlink,
ctx2 IN STRLINK
)
RETURN NUMBER
IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
DEPTNO WM_CONCAT(ENAME)
------ --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
from emp
group by depart
呵呵,是我写sql太大意了,我引用内层循环的变量名字和外层循环判断的字段相同,我改了内层变量名就可以了。
如下:
select city,(
select ***
from table a
where a.city = city)
from (
city,xxxxxxxxxxxxxxxxxxxx
),重点出错在select ***
from table a
where a.city = city,这会导致死循环,然后,我把内层的city改为citya,然后内层改为
select ***
from table a
where a.city = citya,然后就可以了。
和wm_concat相比,listagg可以执行排序,例如
select deptno, listagg(ename,';') within group(order by ename) enames from emp group by deptno;