楼主的数据维度方式变化和普通的不太一样,需要拼串成一个字段。方法一,楼主自己写一个函数(不推荐,性能很低,尤其对于大量数据时,不过为了说明后面的问题,我也提及一下,也是一种解决方法):CREATE OR REPLACE FUNCTION F_CON_STR(v_userID VARCHAR2) RETURN VARCHAR2 IS returnval VARCHAR(8000); v_counter NUMBER; TYPE TABLE_STR IS TABLE OF PERSON.OTHER%TYPE INDEX BY BINARY_INTEGER; V_TABLE_RECODE TABLE_STR; BEGIN SELECT COUNT(1) INTO v_counter FROM PERSON WHERE userID = v_userID; IF v_counter = 0 THEN RETURN ''; END IF; SELECT other BULK COLLECT INTO V_TABLE_RECODE FROM PERSON WHERE userID = v_userID; FOR i IN V_TABLE_RECODE.FIRST .. V_TABLE_RECODE.LAST LOOP returnval := returnval||V_TABLE_RECODE(i)||','; END LOOP; RETURN substr(returnval,1,length(returnval)-1); END F_CON_STR; 然后查询方法:SQL> SELECT userid,groupid,F_CON_STR(userid) 2 FROM person 3 GROUP BY userid,groupid;USERID GROUPID F_CON_STR(USERID) ---------- ---------- ------------------- 2 111 name2,like2 1 111 name1,like1方法2(直接使用系统提供的函数WMSYS.wm_concat,由于其建立了PUBLIC同义词,所以可以直接使用):SQL> select userid,groupid,wm_concat(other) other 2 FROM person 3 GROUP BY userid,groupid; USERID GROUPID OTHER ---------- ---------- ------------------------------------------- 1 111 name1,like1 2 111 name2,like2
RETURN VARCHAR2
IS
returnval VARCHAR(8000);
v_counter NUMBER;
TYPE TABLE_STR IS TABLE OF PERSON.OTHER%TYPE INDEX BY BINARY_INTEGER;
V_TABLE_RECODE TABLE_STR;
BEGIN
SELECT COUNT(1) INTO v_counter
FROM PERSON
WHERE userID = v_userID;
IF v_counter = 0 THEN
RETURN '';
END IF;
SELECT other
BULK COLLECT INTO V_TABLE_RECODE
FROM PERSON
WHERE userID = v_userID;
FOR i IN V_TABLE_RECODE.FIRST .. V_TABLE_RECODE.LAST LOOP
returnval := returnval||V_TABLE_RECODE(i)||',';
END LOOP;
RETURN substr(returnval,1,length(returnval)-1);
END F_CON_STR;
然后查询方法:SQL> SELECT userid,groupid,F_CON_STR(userid)
2 FROM person
3 GROUP BY userid,groupid;USERID GROUPID F_CON_STR(USERID)
---------- ---------- -------------------
2 111 name2,like2
1 111 name1,like1方法2(直接使用系统提供的函数WMSYS.wm_concat,由于其建立了PUBLIC同义词,所以可以直接使用):SQL> select userid,groupid,wm_concat(other) other
2 FROM person
3 GROUP BY userid,groupid; USERID GROUPID OTHER
---------- ---------- -------------------------------------------
1 111 name1,like1
2 111 name2,like2