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

WITH TAS(SELECT 'a' a, 100 b FROM dualUNION ALLSELECT 'b', 100 FROM dualUNION ALLSELECT '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) rnFROM t)START WITH rn=1CONNECT BY rn-1=PRIOR rn and b=prior bGROUP BY b--result:c    90    1a,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=1CONNECT BY rn-1=PRIOR rn and score=prior scoreGROUP 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 TAS(SELECT 'a' a, 100 b FROM dualUNION ALLSELECT 'b', 100 FROM dualUNION ALLSELECT '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;