table1:ID name sex 1 A 1 2 A 1 3 B 1 4 c 1 5 A 1 如何查询得出下边的结果: name sex id A 1 1,2,5 B 1 3 C 1 4我用分组语句可就是不知道怎么将组里的ID进行合并,类似sum(ID)的跨多列的字符串合并函数有没有了/? 请高手问指点指点,谢谢了!
SQL> SELECT * FROM TABLE1; ID NAME SEX ---------- ---------- --- 1 A 1 2 A 1 3 B 1 4 C 1 5 A 1SQL> SELECT NAME, SEX, wmsys.wm_concat(ID) ID FROM TABLE1 GROUP BY NAME, SEX;NAME SEX ID ---------- --- -------------------------------------------------------------------------------- A 1 1,2,5 B 1 3 C 1 4
如果数据库是10g的,试试下面的方法: SELECT NAME, SEX, WMSYS.WM_CONCAT(ID) FROM TABLE1 GROUP BY NAME, SEX;
with testa as (select 1 q, 'a' NO , 1 age from dual UNION ALL select 2 q, 'a' NO , 1 age from dual UNION ALL select 2 q, 'b' NO , 1 age from dual ) select no,age,max(sys_connect_by_path(q,',')) from ( select no,age,q, row_number() over( order by no,age) rn, row_number() over(partition by no,age order by NO,age) rn1 from testa ) start with rn1=1 connect by rn-1=prior rn group by no,age
通用方法(9i/10g): CREATE TABLE table1(ID INT,NAME VARCHAR2(10),sex VARCHAR2(2)); INSERT INTO TABLE1 VALUES (1, 'A', 1); INSERT INTO TABLE1 VALUES (2, 'A', 1); INSERT INTO TABLE1 VALUES (3, 'B', 1); INSERT INTO TABLE1 VALUES (4, 'c', 1); INSERT INTO TABLE1 VALUES (5, 'A', 1);SELECT NAME, SEX, MAX(x) ID FROM (SELECT NAME, SEX, LTRIM(SYS_CONNECT_BY_PATH(ID, ','), ',') x FROM (SELECT NAME, SEX, ID, ROW_NUMBER() OVER(PARTITION BY NAME, SEX ORDER BY ID) AS CURR, ROW_NUMBER() OVER(PARTITION BY NAME, SEX ORDER BY ID) - 1 AS PREV FROM TABLE1) START WITH CURR = 1 CONNECT BY PREV = PRIOR CURR AND NAME = PRIOR NAME AND SEX = PRIOR SEX) GROUP BY NAME, SEX ORDER BY 1; 输出: NAME SEX ID A 1 1,2,5 B 1 3 c 1 4
---------- ---------- ---
1 A 1
2 A 1
3 B 1
4 C 1
5 A 1SQL> SELECT NAME, SEX, wmsys.wm_concat(ID) ID FROM TABLE1 GROUP BY NAME, SEX;NAME SEX ID
---------- --- --------------------------------------------------------------------------------
A 1 1,2,5
B 1 3
C 1 4
解决的办法:就是对以name为分组条件,然后id通过函数求值,也就是相同的name通过函数返回id的字符串之和
SELECT NAME, SEX, WMSYS.WM_CONCAT(ID) FROM TABLE1 GROUP BY NAME, SEX;
from dual
UNION ALL
select 2 q, 'a' NO , 1 age
from dual
UNION ALL
select 2 q, 'b' NO , 1 age
from dual
) select no,age,max(sys_connect_by_path(q,',')) from (
select no,age,q,
row_number() over( order by no,age) rn,
row_number() over(partition by no,age order by NO,age) rn1
from testa
)
start with rn1=1
connect by rn-1=prior rn
group by no,age
10g就用函数WMSYS.WM_CONCAT()
CREATE TABLE table1(ID INT,NAME VARCHAR2(10),sex VARCHAR2(2));
INSERT INTO TABLE1 VALUES (1, 'A', 1);
INSERT INTO TABLE1 VALUES (2, 'A', 1);
INSERT INTO TABLE1 VALUES (3, 'B', 1);
INSERT INTO TABLE1 VALUES (4, 'c', 1);
INSERT INTO TABLE1 VALUES (5, 'A', 1);SELECT NAME, SEX, MAX(x) ID
FROM (SELECT NAME,
SEX,
LTRIM(SYS_CONNECT_BY_PATH(ID, ','), ',') x
FROM (SELECT NAME,
SEX,
ID,
ROW_NUMBER() OVER(PARTITION BY NAME, SEX ORDER BY ID) AS CURR,
ROW_NUMBER() OVER(PARTITION BY NAME, SEX ORDER BY ID) - 1 AS PREV
FROM TABLE1)
START WITH CURR = 1
CONNECT BY PREV = PRIOR CURR
AND NAME = PRIOR NAME
AND SEX = PRIOR SEX)
GROUP BY NAME, SEX
ORDER BY 1;
输出:
NAME SEX ID
A 1 1,2,5
B 1 3
c 1 4
多谢各位前辈!
消息 4121,级别 16,状态 1,第 1 行
找不到列 "wmsys" 或用户定义的函数或聚合 "wmsys.wm_concat",或者名称不明确。