wm_concat 分组->倒序->排重->取前两条拼接

[cmdares]

需求：需要得到每组中最近时间出现的人名前2名
如题求教一条sql
A(分组) B(排重拼接) C(时间倒序)
------------------------------------------------------
A 张三 2012-01-01
A 张三 2012-01-02
A 李四 2012-01-03
A 王五 2012-01-04
A 王五 2012-01-05
A 赵六 2012-01-06
B 王五 2012-01-01
B 王五 2012-01-02
B 赵六 2012-01-03
C 李四 2012-01-01
C 王五 2012-01-02
C 王五 2012-01-03
针对上述数据需要生成如下结果:
A 赵六,王五
B 赵六,王五
C 王五,李四
想了好久不得其解，求教高手这个逻辑应该怎么写，谢谢

[小海葵1]

现在都在家上网，带孩子了。

[C18]

with sd as (select 'A' as id,'张三' as name,'2012-01-01' as datadate from dual union all
select 'A' as id,'张三' as name,'2012-01-02' as datadate from dual union all
select 'A' as id,'李四' as name,'2012-01-03' as datadate from dual union all
select 'A' as id,'王五' as name,'2012-01-04' as datadate from dual union all
select 'A' as id,'王五' as name,'2012-01-05' as datadate from dual union all
select 'A' as id,'赵六' as name,'2012-01-06' as datadate from dual union all
select 'B' as id,'王五' as name,'2012-01-01' as datadate from dual union all
select 'B' as id,'王五' as name,'2012-01-02' as datadate from dual union all
select 'B' as id,'赵六' as name,'2012-01-03' as datadate from dual union all
select 'C' as id,'李四' as name,'2012-01-01' as datadate from dual union all
select 'C' as id,'王五' as name,'2012-01-02' as datadate from dual union all
select 'C' as id,'王五' as name,'2012-01-03' as datadate from dual)
select t.id, wm_concat(t.name) as names
 from (select d.id,
       d.name,
       /*max(d.datadate) as datadate,*/
       row_number() over(partition by d.id order by max(d.datadate) desc) as seq
  from sd d
 group by d.id, d.name) t where t.seq<3 group by t.id;

[lh412552703]

我只分享思路。 *列转行 *行转列 *多列转换成字符串 *多行转换成字符串 *字符串转换成多列 *字符串转换成多行 下面分别进行举例介绍。 首先声明一点，有些例子需要如下10g及以后才有的知识： a。掌握model子句， b。正则表达式 c。加强的层次查询 讨论的适用范围只包括8i,9i,10g及以后版本。begin： 1、列转行 CREATE TABLE t_col_row( ID INT, c1 VARCHAR2(10), c2 VARCHAR2(10), c3 VARCHAR2(10)); INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31'); INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL); INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33'); INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34'); INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL); INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35'); INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL); COMMIT; SELECT * FROM t_col_row; 1)UNION ALL 适用范围：8i,9i,10g及以后版本 SELECT id, 'c1' cn, c1 cv   FROM t_col_row UNION ALL SELECT id, 'c2' cn, c2 cv   FROM t_col_row UNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row; 若空行不需要转换，只需加一个where条件， WHERE COLUMN IS NOT NULL 即可。 2)MODEL 适用范围：10g及以后 SELECT id, cn, cv FROM t_col_row MODEL RETURN UPDATED ROWS PARTITION BY (ID) DIMENSION BY (0 AS n) MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3) RULES UPSERT ALL (   cn[1] = 'c1',   cn[2] = 'c2',   cn[3] = 'c3',   cv[1] = c1[0],   cv[2] = c2[0],   cv[3] = c3[0]   ) ORDER BY ID,cn; 3)collection 适用范围：8i,9i,10g及以后版本 要创建一个对象和一个集合： CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10)); CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair; SELECT id, t.cn AS cn, t.cv AS cv   FROM t_col_row,        TABLE(cv_varr(cv_pair('c1', t_col_row.c1),                      cv_pair('c2', t_col_row.c2),                      cv_pair('c3', t_col_row.c3))) t ORDER BY 1, 2;

[xw5360]

with sd as (select 'A' as id,'張三' as name,'2012-01-01' as datadate from dual union all select 'A' as id,'張三' as name,'2012-01-02' as datadate from dual union all select 'A' as id,'李四' as name,'2012-01-03' as datadate from dual union all select 'A' as id,'王五' as name,'2012-01-04' as datadate from dual union all select 'A' as id,'王五' as name,'2012-01-05' as datadate from dual union all select 'A' as id,'趙六' as name,'2012-01-06' as datadate from dual union all select 'B' as id,'王五' as name,'2012-01-01' as datadate from dual union all select 'B' as id,'王五' as name,'2012-01-02' as datadate from dual union all select 'B' as id,'趙六' as name,'2012-01-03' as datadate from dual union all select 'C' as id,'李四' as name,'2012-01-01' as datadate from dual union all select 'C' as id,'趙六' as name,'2012-01-02' as datadate from dual union all select 'C' as id,'王五' as name,'2012-01-03' as datadate from dual) select id ,wm_concat(name) from( select id,name from( select id,name,datadate,row_number() over(partition by id order by datadate desc) num1 from sd ) where num1 in (1,3) ) group by id ----------------------------------------------------- 1 A 趙六,王五 2 B 趙六,王五 3 C 王五,李四

[Galen_Gao]

感觉楼上的还是有问题，假如一个组里前面那几个名单都是同一人，比如C组王五连续最近时间都是他，你们的where那个就狭窄了，这时C组会有两个王五，如果增大where条件，那么其他组就会不止两个人，如何解决？？

引用 2 楼 eppe6666 的回复:

SQL code?12345678910111213141516171819with sd as (select 'A' as id,'张三' as name,'2012-01-01' as datadate from dual union allselect 'A' as id,'张三' as name,'2012-01-02' as datadate from dua……

[小海葵1]

借用2楼的数据 select wm_concat(name) as names from (select row_number() over(partition by id order by datadate desc) as rn, id, name, datadate from (select row_number() over(partition by id, name order by datadate desc) as rn1, id, name, datadate from sd) where rn1 = 1) where rn < 3 group by id ========================================= 1 趙六,王五 2 趙六,王五 3 王五,李四