求教一个oracle的sql查询，需要输出多行数据但不知道该怎么做?

Question

需求是这样，已知每日考勤会生成一条带时间的记录，现在要求提取出所有人的考勤记录，详细显示出打卡和未打卡的情况。

我先做了一张动态日历，代码如下：

select everyDay,
to_char(everyday,'dy') as 星期几,
lpad(to_char(everyday,'w'),6) as 该月的第几周,
lpad(to_char(everyday,'ww'),6) as 该年的第几周
from(select to_date('20150901','yyyymmdd') + level - 1 as everyDay from dual
connect by level <=
(last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))

考勤表筛选出单个员工当月的考勤数据：

select distinct STARTTIME,Peoplename
from TB_DAYWORK
where CREATETIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss')
and PEOPLENAME=‘xxx’ order by 1;

以上两个sql做左外链接，得到该员工当月考勤情况，空即未打卡：

select m.* ,n.starttime,n.peoplename from ( select everyDay,to_char(everyday,'dy') as 星期几,lpad(to_char(everyday,'w'),6) as 该月的第几周,lpad(to_char(everyday,'ww'),6) as 该年的第几周 from(select to_date('20150901','yyyymmdd') + level - 1 as everyDay from dual connect by level <= (last_day(to_date('20150901','yyyymmdd')) - to_date('20150901','yyyymmdd') +1))) m left join (select distinct STARTTIME,Peoplenamefrom TB_DAYWORK where CREATETIME between to_date('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-09-30 23:59:59','yyyy-mm-dd hh24:mi:ss') and PEOPLENAME in (select leadername from leader where num=1) ) n on m.EVERYDAY=n.STARTTIMEorder by 1;

现在想把整个一个部门的人的打卡情况全部拉出来。例如某部门人员信息表名为leader，员工姓名字段为leadername。

求教sql该如何写才能列出多行数据？ 试过循环语句和cursor但都不对。真心求助。。。。

Answer 1

使用listagg函数可以解决。。。sql代码如下：

ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE';

select j.* from (
   
    select a.* from (

select leadername,listagg(考勤,',') within group(order by everyday) as calendar
from 
(


    select tmp.* , n.starttime, nvl2(n.peoplename,' ','X') as 考勤
    from
    ( select m.*,l.*
        from (
            select
                everyDay,
                to_char(everyday,'dy') as weekday,
                lpad(to_char(everyday,'w'),6) as weekinmonth,
                lpad(to_char(everyday,'ww'),6) as weekinyear
            from
              (select to_date('20161120','yyyymmdd') + level - 1 as everyDay
               from dual
               connect by level <= (last_day(to_date('20161220','yyyymmdd')) - to_date('20161201','yyyymmdd') +1))
           ) m,
           leader l ) tmp
          left join
          (
            select  t.STARTTIME,
                    t.Peoplename
            from  TB_DAYWORK t
            where t.STARTTIME between to_date('2016-11-20 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2016-12-20 23:59:59','yyyy-mm-dd hh24:mi:ss')
              ) n
    on tmp.EVERYDAY=n.STARTTIME and tmp.leadername=n.peoplename
    order by tmp.num,tmp.everyDay ) group by leadername) a
    full join leader b on a.leadername=b.leadername
    
    union
    
     select nvl2(leadername,'',''), calendar from (
select leadername,listagg(to_char(everyday,'yyyy-mm-dd') ,',') within group ( order by everyday) as calendar from
(


    select tmp.* , n.starttime, nvl2(n.peoplename,' ','X') as 考勤
    from
    ( select m.*,l.*
        from (
            select
                everyDay,
                to_char(everyday,'dy') as weekday,
                lpad(to_char(everyday,'w'),6) as weekinmonth,
                lpad(to_char(everyday,'ww'),6) as weekinyear
            from
              (select to_date('20161120','yyyymmdd') + level - 1 as everyDay
               from dual
               connect by level <= (last_day(to_date('20161220','yyyymmdd')) - to_date('20161201','yyyymmdd') +1))
           ) m,
           leader l ) tmp
          left join
          (
            select  t.STARTTIME,
                    t.Peoplename
            from  TB_DAYWORK t
            where t.STARTTIME between to_date('2016-11-20 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2016-12-20 23:59:59','yyyy-mm-dd hh24:mi:ss')
              ) n
    on tmp.EVERYDAY=n.STARTTIME and tmp.leadername=n.peoplename
    order by tmp.num,tmp.everyDay ) group by leadername
    
    union
    
    select leadername,listagg(weekday ,',') within group ( order by everyday) as calendar from
(


    select tmp.* , n.starttime, nvl2(n.peoplename,' ','X') as 考勤
    from
    ( select m.*,l.*
        from (
            select
                everyDay,
                to_char(everyday,'dy') as weekday,
                lpad(to_char(everyday,'w'),6) as weekinmonth,
                lpad(to_char(everyday,'ww'),6) as weekinyear
            from
              (select to_date('20161120','yyyymmdd') + level - 1 as everyDay
               from dual
               connect by level <= (last_day(to_date('20161220','yyyymmdd')) - to_date('20161201','yyyymmdd') +1))
           ) m,
           leader l ) tmp
          left join
          (
            select  t.STARTTIME,
                    t.Peoplename
            from  TB_DAYWORK t
            where t.STARTTIME between to_date('2016-11-20 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2016-12-20 23:59:59','yyyy-mm-dd hh24:mi:ss')
              ) n
    on tmp.EVERYDAY=n.STARTTIME and tmp.leadername=n.peoplename

    order by tmp.num,tmp.everyDay ) group by leadername
    
    )
    where rownum<=2
    
  ) j 
  full join leader k on j.leadername=k.leadername order by k.num nulls first;