WITH T AS ( SELECT 'a' a, 100 b FROM dual UNION ALL SELECT 'b', 100 FROM dual UNION ALL SELECT 'c', 90 FROM dual ) SELECT MAX(substr(sys_connect_by_path(a,','),2)) 人员,b 分数,count(b) 人数 FROM ( SELECT a,b,row_number()over(PARTITION BY b ORDER BY a) rn FROM t ) START WITH rn=1 CONNECT BY rn-1=PRIOR rn and b=prior b GROUP BY b --result: c 90 1 a,b 100 2
实测成功:CREATE TABLE T22 ( NAME VARCHAR2(20), score NUMBER(4) );INSERT INTO T22 VALUES('a', 100); INSERT INTO T22 VALUES('b', 100); INSERT INTO T22 VALUES('c', 90); INSERT INTO T22 VALUES('d', 80); INSERT INTO T22 VALUES('e', 80); INSERT INTO T22 VALUES('f', 80); INSERT INTO T22 VALUES('g', 75); SELECT MAX(substr(sys_connect_by_path(NAME,','),2)) 姓名, score 分数, count(score) 人数 FROM (SELECT NAME, score, row_number()over(PARTITION BY score ORDER BY NAME) rn FROM T22) START WITH rn=1 CONNECT BY rn-1=PRIOR rn and score=prior score GROUP BY score; 结果:
SQL> create table t 2 as 3 3 SELECT 'a' a, 100 b FROM dual 4 UNION ALL 5 SELECT 'b', 100 FROM dual 6 UNION ALL 7 SELECT 'c', 90 FROM dual 8 ;
Table created
SQL> select wm_concat(a) a,b,count(*) from t group by b;
A B COUNT(*) -------------------------------------------------------------------------------- ---------- ---------- c 90 1 a,b 100 2
SQL>
10g以后可以用wm_concat WITH T AS ( SELECT 'a' a, 100 b FROM dual UNION ALL SELECT 'b', 100 FROM dual UNION ALL SELECT 'c', 90 FROM dual ) select wm_concat(a) a,b,count(*) from t group by b;
參照方法/**--合并
--模拟数据 Col1 Col2 1 a 1 b 1 c 2 d 2 e 3 f **/
/**--生成结果
COL1 COL2 1 a,b,c 2 d,e 3 f **/
/**oracle10g以上版本字符串函数wmsys.wm_concat**/
/**方法1**/ with Tab as ( select 1 as Col1,'a' as Col2 from dual union all select 1,'b' from dual union all select 1,'c' from dual union all select 2,'d' from dual union all select 2,'e' from dual union all select 3,'f' from dual ) select Col1,wmsys.wm_concat(Col2 ) as Col2 from tab group by Col1
/**oracle9i可以用connect by**/
/**方法2**/ with Tab as ( select 1 as Col1,'a' as Col2 from dual union all select 1,'b' from dual union all select 1,'c' from dual union all select 2,'d' from dual union all select 2,'e' from dual union all select 3,'f' from dual ) select Col1,substr(max(sys_connect_by_path(Col2,',')),2) Col2 from (select a.*,row_number()over(partition by Col1 order by Col1) rn from Tab a ) group by Col1 start with rn=1 connect by rn-1=prior rn and Col1=prior Col1 order by Col1;
直接 SELECT WM_CONCAT(NAME) NAME,分数,COUNT(分数) FROM table_name GROUP BY 分数; 不好么?? 前面那一大坨是什么啊???
AS
(
SELECT 'a' a, 100 b FROM dual
UNION ALL
SELECT 'b', 100 FROM dual
UNION ALL
SELECT 'c', 90 FROM dual
)
SELECT MAX(substr(sys_connect_by_path(a,','),2)) 人员,b 分数,count(b) 人数
FROM
(
SELECT a,b,row_number()over(PARTITION BY b ORDER BY a) rn
FROM t
)
START WITH rn=1
CONNECT BY rn-1=PRIOR rn and b=prior b
GROUP BY b
--result:
c 90 1
a,b 100 2
(
NAME VARCHAR2(20),
score NUMBER(4)
);INSERT INTO T22 VALUES('a', 100);
INSERT INTO T22 VALUES('b', 100);
INSERT INTO T22 VALUES('c', 90);
INSERT INTO T22 VALUES('d', 80);
INSERT INTO T22 VALUES('e', 80);
INSERT INTO T22 VALUES('f', 80);
INSERT INTO T22 VALUES('g', 75);
SELECT MAX(substr(sys_connect_by_path(NAME,','),2)) 姓名, score 分数, count(score) 人数
FROM (SELECT NAME, score, row_number()over(PARTITION BY score ORDER BY NAME) rn FROM T22)
START WITH rn=1
CONNECT BY rn-1=PRIOR rn and score=prior score
GROUP BY score;
结果:
2 as
3
3 SELECT 'a' a, 100 b FROM dual
4 UNION ALL
5 SELECT 'b', 100 FROM dual
6 UNION ALL
7 SELECT 'c', 90 FROM dual
8 ;
Table created
SQL> select wm_concat(a) a,b,count(*) from t group by b;
A B COUNT(*)
-------------------------------------------------------------------------------- ---------- ----------
c 90 1
a,b 100 2
SQL>
WITH T
AS
(
SELECT 'a' a, 100 b FROM dual
UNION ALL
SELECT 'b', 100 FROM dual
UNION ALL
SELECT 'c', 90 FROM dual
)
select wm_concat(a) a,b,count(*) from t group by b;
--模拟数据
Col1 Col2
1 a
1 b
1 c
2 d
2 e
3 f
**/
/**--生成结果
COL1 COL2
1 a,b,c
2 d,e
3 f
**/
/**oracle10g以上版本字符串函数wmsys.wm_concat**/
/**方法1**/
with Tab
as
(
select 1 as Col1,'a' as Col2 from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 2,'d' from dual union all
select 2,'e' from dual union all
select 3,'f' from dual
)
select
Col1,wmsys.wm_concat(Col2 ) as Col2
from tab group by Col1
/**oracle9i可以用connect by**/
/**方法2**/
with Tab
as
(
select 1 as Col1,'a' as Col2 from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 2,'d' from dual union all
select 2,'e' from dual union all
select 3,'f' from dual
)
select
Col1,substr(max(sys_connect_by_path(Col2,',')),2) Col2
from
(select a.*,row_number()over(partition by Col1 order by Col1) rn from Tab a )
group by Col1 start with rn=1
connect by rn-1=prior rn and Col1=prior Col1
order by Col1;
SELECT WM_CONCAT(NAME) NAME,分数,COUNT(分数) FROM table_name GROUP BY 分数;
不好么??
前面那一大坨是什么啊???