CREATE TABLE A(userno VARCHAR2(20),username VARCHAR2(20)); INSERT INTO A VALUES('A1001','张三'); INSERT INTO A VALUES('A1001','李四'); INSERT INTO A VALUES('A1001','五五'); SELECT userno,wmsys.wm_concat(username) FROM a GROUP BY userno
SELECT "账号",WMSYS.WM_CONCAT("名称") AS "名称" FROM A GROUP BY "账号"如果账号之间必须使用+,就使用REPLACE将产生后的名称进行替换. 我这是9I啊?9I开始Oracle提供了SYS_CONNECT_BY_PATH函数,可以实现上述功能.能否给具体写法呢? 像这样的现在有个table A 存的数据如下:id id_name no no_name amt adress A001 小张 20130729 zcr001 5000 北京 A001 小张 20130729 zcr001 1000 上海现在我想取的结果是这样的: id id_name no no_name amt adress A001 小张 20130729 zcr001 6000 北京,上海 是oracle 9I。
SELECT id,id_name,no,no_name,sum(amt),wm_concat(adress) from tableA GROUP BY id,id_name,no,no_name
SELECT "账号",WMSYS.WM_CONCAT("名称") AS "名称" FROM A GROUP BY "账号"如果账号之间必须使用+,就使用REPLACE将产生后的名称进行替换. 我这是9I啊?9I开始Oracle提供了SYS_CONNECT_BY_PATH函数,可以实现上述功能.能否给具体写法呢? 像这样的现在有个table A 存的数据如下:id id_name no no_name amt adress A001 小张 20130729 zcr001 5000 北京 A001 小张 20130729 zcr001 1000 上海现在我想取的结果是这样的: id id_name no no_name amt adress A001 小张 20130729 zcr001 6000 北京,上海 是oracle 9I。借用5#数据,脚本和SQL如下:/*脚本 DROP TABLE A PURGE; CREATE TABLE A(userno VARCHAR2(20),username VARCHAR2(20)); INSERT INTO A VALUES('A1001','张三'); INSERT INTO A VALUES('A1001','李四'); INSERT INTO A VALUES('A1001','五五'); INSERT INTO A VALUES('A1002','张三2'); INSERT INTO A VALUES('A1002','李四2'); INSERT INTO A VALUES('A1003','五五3'); INSERT INTO A VALUES('A1003','张三3'); INSERT INTO A VALUES('A1003','李四3'); INSERT INTO A VALUES('A1003','五五3'); */ SELECT UO, SUBSTR(MAX(SYS_CONNECT_BY_PATH(T.UN,'+')),2) NAME FROM ( SELECT A.USERNO UO, A.USERNAME UN, ROW_NUMBER() OVER(PARTITION BY USERNO ORDER BY USERNO) RN FROM A ) T START WITH RN=1 CONNECT BY RN=PRIOR RN+1 AND UO=PRIOR UO GROUP BY UO ORDER BY UO
SELECT "账号",WMSYS.WM_CONCAT("名称") AS "名称"
FROM A
GROUP BY "账号"如果账号之间必须使用+,就使用REPLACE将产生后的名称进行替换.
我这是9I啊?
SELECT "账号",WMSYS.WM_CONCAT("名称") AS "名称"
FROM A
GROUP BY "账号"如果账号之间必须使用+,就使用REPLACE将产生后的名称进行替换.
我这是9I啊?9I开始Oracle提供了SYS_CONNECT_BY_PATH函数,可以实现上述功能.
第二步:用wmsys.wm_concat可以达到 "小张,张三,李四"
第三步:用replace函数将逗号替换为'+'
INSERT INTO A VALUES('A1001','张三');
INSERT INTO A VALUES('A1001','李四');
INSERT INTO A VALUES('A1001','五五');
SELECT userno,wmsys.wm_concat(username) FROM a
GROUP BY userno
SELECT "账号",WMSYS.WM_CONCAT("名称") AS "名称"
FROM A
GROUP BY "账号"如果账号之间必须使用+,就使用REPLACE将产生后的名称进行替换.
我这是9I啊?9I开始Oracle提供了SYS_CONNECT_BY_PATH函数,可以实现上述功能.能否给具体写法呢?
像这样的现在有个table A 存的数据如下:id id_name no no_name amt adress
A001 小张 20130729 zcr001 5000 北京
A001 小张 20130729 zcr001 1000 上海现在我想取的结果是这样的:
id id_name no no_name amt adress
A001 小张 20130729 zcr001 6000 北京,上海
是oracle 9I。
from tableA
GROUP BY id,id_name,no,no_name
SELECT "账号",WMSYS.WM_CONCAT("名称") AS "名称"
FROM A
GROUP BY "账号"如果账号之间必须使用+,就使用REPLACE将产生后的名称进行替换.
我这是9I啊?9I开始Oracle提供了SYS_CONNECT_BY_PATH函数,可以实现上述功能.能否给具体写法呢?
像这样的现在有个table A 存的数据如下:id id_name no no_name amt adress
A001 小张 20130729 zcr001 5000 北京
A001 小张 20130729 zcr001 1000 上海现在我想取的结果是这样的:
id id_name no no_name amt adress
A001 小张 20130729 zcr001 6000 北京,上海
是oracle 9I。借用5#数据,脚本和SQL如下:/*脚本
DROP TABLE A PURGE;
CREATE TABLE A(userno VARCHAR2(20),username VARCHAR2(20));
INSERT INTO A VALUES('A1001','张三');
INSERT INTO A VALUES('A1001','李四');
INSERT INTO A VALUES('A1001','五五');
INSERT INTO A VALUES('A1002','张三2');
INSERT INTO A VALUES('A1002','李四2');
INSERT INTO A VALUES('A1003','五五3');
INSERT INTO A VALUES('A1003','张三3');
INSERT INTO A VALUES('A1003','李四3');
INSERT INTO A VALUES('A1003','五五3');
*/
SELECT UO,
SUBSTR(MAX(SYS_CONNECT_BY_PATH(T.UN,'+')),2) NAME
FROM
(
SELECT A.USERNO UO,
A.USERNAME UN,
ROW_NUMBER() OVER(PARTITION BY USERNO ORDER BY USERNO) RN
FROM A
) T
START WITH RN=1
CONNECT BY RN=PRIOR RN+1
AND UO=PRIOR UO
GROUP BY UO
ORDER BY UO
A1001 张三+李四+五五
A1002 张三2+李四2
A1003 五五3+张三3+李四3+五五3
select 账号,replace(名称 ,',','+') as 名称 from (SELECT account as "账号",WM_CONCAT(name) AS "名称"
FROM log_user
GROUP BY account) ;
执行结果:
from xxx
group by id;
FROM A
GROUP BY "账号"