重复字段 重命名 和 count 如何写

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

------解决方案--------------------
实测成功：

SQL code

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

SQL code

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;

------解决方案--------------------
參照方法

SQL code

/**--合并  
  
--模拟数据  
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;