非空字符串连接问题

Question

现有一张表记录每个人每天的每项工作情况，现在我想查询获得每个人最近的工作内容，并按照工作项目排序(JOB1,JOB2...)后连接成一个完整字符串（如果某项工作是空的话，考虑使用一个占位符）。
例如：
name    job1    job2    sys_date
张三    上课     上网    20110504
张三    吃饭     null    20110505
张三    null     测试    20110506查询得到的结果应为：
name    job
张三    吃饭,测试我自己稍微写了下，但是比较复杂，不知道有没有比较简单的解决方案。另外也没有实现对空的项目使用占位符（假如张三job1项全是null的话，获得的结果应该类似：null,测试）。望各位大侠群策群力，谢谢了。with t as (
select '张三' name,'上课' job1,'上网' job2,'20110504' sys_date from dual union all
select '张三' name,'吃饭' job1,null job2,'20110505' sys_date from dual union all
select '张三' name,null job1,'测试' job2,'20110506' sys_date from dual union all
select '李四' name,'吃饭' job1,'开会' job2,'20110505' sys_date from dual union all
select '王五' name, null job1,null job2,'20110505' sys_date from dual union all
select '王五' name, '学车' job1,null job2,'20110506' sys_date from dual union all
select '马六' name, '开店' job1,null job2,'20110507' sys_date from dual
)
select name,max(job) job from (
--连接JOB1和JOB2
select name,wm_concat(job) over(partition by name order by type) job from (--获得JOB1不为空的第一条记录
select * from(
select name,job1 job,'1' type,
rank() over(partition by name order by sys_date desc) rn from t 
where job1 is not null 
) where rn=1union all--获得JOB2不为空的第一条记录
select * from(
select name,job2 work,'2' type,
rank() over(partition by name order by sys_date desc) rn from t 
where job2 is not null
) where rn=1
)
)group by name;
    

Answer 1

with t as (
     select '张三' name,'上课' job1,'上网' job2,to_date('20110504','yyyymmdd') sys_date from dual union all
     select '张三','吃饭',null,to_date('20110505','yyyymmdd') from dual union all
     select '张三',null,'测试',to_date('20110506','yyyymmdd') from dual union all
     select '李四','吃饭','开会',to_date('20110505','yyyymmdd') from dual union all
     select '王五',null,null,to_date('20110505','yyyymmdd') from dual union all
     select '王五','学车',null,to_date('20110506','yyyymmdd') from dual union all
     select '马六','开店',null,to_date('20110507','yyyymmdd') from dual)
select name,
       wm_concat(job)
from (
     select name,
            job1||','||job2 job
     from t
     where job1 is not null
        or job2 is not null)
group by name
/
NAME WM_CONCAT(JOB)
---- --------------------------------------------------------------------------------
李四 吃饭,开会
马六 开店,
王五 学车,
张三 上课,上网,吃饭,,,测试

Answer 2

需要查询获得的是每个人最近的工作情况，历史的信息就不需要了。简单来说，就是按人分组，然后按sys_date倒序排序，然后遍历每一列(JOB1,JOB2...)，得到每列的第一条非空字符串，最后把这些非空字符串连接起来就好了。如果用存储过程实现的话应该比较容易，但是那样的话效率就太差了，要每个人都要遍历一遍。

Answer 3

SQL> with t as (
  2       select '张三' name,'上课' job1,'上网' job2,'20110504' sys_dt from dual union all
  3       select '张三','吃饭',null,'20110505' from dual union all
  4       select '张三',null,'测试','20110506' from dual union all
  5       select '李四','吃饭','开会','20110505' from dual union all
  6       select '王五',null,null,'20110505' from dual union all
  7       select '王五','学车',null,'20110506' from dual union all
  8       select '马六','开店',null,'20110507' from dual)
  9  select name,
 10         max(job) job
 11  from (
 12       select name,
 13              wm_concat(job1||','||job2) over(partition by name order by sys_dt) job
 14       from t
 15       where job1 is not null
 16          --你可以在这里添加你想要的日期区段:and sys_dt between sysdt1 and sysdt2
 17          )
 18  group by name
 19  /NAME JOB
---- --------------------------------------------------------------------------------
李四 吃饭,开会
马六 开店,
王五 学车,
张三 上课,上网,吃饭,

Answer 4

你好，我可能描述的不太清楚。举例来说，
name    job1    job2     sys_date
aaa     null    上课     20110509
aaa     null    null     20110508
aaa     开业    睡觉     20110507      
...
那么aaa这个人对于job1项目来说最近的工作内容（第一条非空记录）为：开业，对于job2项目来说最近的工作内容为：上课那么最终的结果应该为：
name    job
aaa     开业,上课不知道这样描述是否清楚了。再次感谢你的回复。

Answer 5

select name,
       wm_concat(job)
from (
     select name,
            job1
     from t where (name,sys_date) =(
     select name,
            max(sys_date) sys_date
     from t
     where job1 is not null
     group by name)
     union
     select name,
            job2
     from t where (name,sys_date) =(
     select name,
            max(sys_date) sys_date
     from t
     where job2 is not null
     group by name)
      )
group by name

Answer 6

SQL> with t as (
  2       select '张三' name,'上课' job1,'上网' job2,to_date('20110504','yyyymmdd') sys_date from du
al union all
  3       select '张三','吃饭',null,to_date('20110505','yyyymmdd') from dual union all
  4       select '张三',null,'测试',to_date('20110506','yyyymmdd') from dual union all
  5       select '李四','吃饭','开会',to_date('20110505','yyyymmdd') from dual union all
  6       select '王五',null,null,to_date('20110505','yyyymmdd') from dual union all
  7       select '王五','学车',null,to_date('20110506','yyyymmdd') from dual union all
  8       select '马六','开店',null,to_date('20110507','yyyymmdd') from dual)
  9    select h.name "员工姓名",substr(h.a,0,decode(instr(h.a,','),0,1000,instr(h.a,',')))
 10    ||','||substr(h.b,0,decode(instr(h.b,','),0,1000,instr(h.a,','))) "工作内容"
 11    from (
 12    select distinct z.name,
 13    last_value(z.job1) over(partition by z.name) a,
 14    last_value(z.job2) over(partition by z.name) b
 15    from
 16  (select name,
 17    wm_concat(job1) over(partition by name order by sys_date desc ) job1,
 18    wm_concat(job2) over(partition by name order by sys_date desc ) job2
 19    from t) z
 20  ) h;员工 工作内容
---- -----------------------------------------------------------------------------------------------
张三 吃饭,,测试,
李四 吃饭,开会
王五 学车,
马六 开店,SQL>   

Answer 7

改下去掉中间的两个',',末尾的逗号也可以类似的去掉
substr(h.b,0,decode(instr(h.b,','),0,1000,instr(h.a,',')-1))SQL>   with t as (
  2       select '张三' name,'上课' job1,'上网' job2,to_date('20110504','yyyymmdd') sys_date from du
al union all
  3       select '张三','吃饭',null,to_date('20110505','yyyymmdd') from dual union all
  4       select '张三',null,'测试',to_date('20110506','yyyymmdd') from dual union all
  5       select '李四','吃饭','开会',to_date('20110505','yyyymmdd') from dual union all
  6       select '王五',null,null,to_date('20110505','yyyymmdd') from dual union all
  7       select '王五','学车',null,to_date('20110506','yyyymmdd') from dual union all
  8       select '马六','开店',null,to_date('20110507','yyyymmdd') from dual)
  9    select h.name "员工姓名",substr(h.a,0,decode(instr(h.a,','),0,1000,instr(h.a,',')-1))
 10    ||','||substr(h.b,0,decode(instr(h.b,','),0,1000,instr(h.a,',')-1)) "工作内容"
 11    from (
 12    select distinct z.name,
 13    last_value(z.job1) over(partition by z.name) a,
 14    last_value(z.job2) over(partition by z.name) b
 15    from
 16  (select name,
 17    wm_concat(job1) over(partition by name order by sys_date desc ) job1,
 18    wm_concat(job2) over(partition by name order by sys_date desc ) job2
 19    from t) z
 20  ) h;员工 工作内容
---- -----------------------------------------------------------------------------------------------
张三 吃饭,测试
李四 吃饭,开会
王五 学车,
马六 开店,SQL>   

Answer 8

好的，多谢了。CSDN上还是牛人多，再次感谢大家的关注。

Answer 9

这样也可以...with t as (
select '张三' name,'上课' job1,'上网' job2,'20110504' sys_date from dual union all
select '张三' name,'吃饭' job1,null job2,'20110505' sys_date from dual union all
select '张三' name,null job1,'测试' job2,'20110506' sys_date from dual union all
select '李四' name,'吃饭' job1,'开会' job2,'20110505' sys_date from dual union all
select '王五' name, null job1,null job2,'20110505' sys_date from dual union all
select '王五' name, '学车' job1,null job2,'20110506' sys_date from dual union all
select '马六' name, '开店' job1,null job2,'20110507' sys_date from dual union all
select '钱七' name, null job1,'出差' job2,'20110507' sys_date from dual
)
select h.name "员工姓名",
       substr(h.a, 0, decode(instr(h.a, ','), 0, 1000, instr(h.a, ',') - 1)) || ',' ||
       substr(h.b, 0, decode(instr(h.b, ','), 0, 1000, instr(h.b, ',') - 1)) "工作内容"
  from (select name, max(job1) a, max(job2) b
          from (select name,
                       wm_concat(job1) over(partition by name order by sys_date desc) job1,
                       wm_concat(job2) over(partition by name order by sys_date desc) job2
                  from t)
         group by name) h;