难写的结果格式

Question

我的detail data 如下histdate    name   type
2011/09/01  张三   请假
2011/09/01  李四   加班
2011/09/06  王     加班
2011/09/30  赵     请假最后的结果显示：title
------------------------------------
星期日       星期一   星期二    星期三    星期四    星期五    星期六
请假|加班 请假|加班 请假|加班 请假|加班 请假|加班 请假|加班 请假|加班 
------------------------------------
                                        9/1       9/2        9/3
                                        张三 李四  
9/6          9/7       9/8        9/9        9/10    9/11      9/12
王
。
。
9/25         9/26      9/27      9/28     9/29      9/30  
                                                        找整体的格式类似月份表
title是星期，下面是查询的结果
每一周的第一行是日期，然后下面分别跟着加班请假的人名这种sql有什么好的方法实现

Answer 1

这是交叉表好吧。为什么要用sql实现？

Answer 2

横向行转列 纵向group by 多重表头不可能直接查询出来，要拼接，这种东西都问了十几年了。

Answer 3

相在程序中显示，就不需要写这么复杂的SQL语句了。
可以将很多处理逻辑放到程序代码中。

Answer 4

 这个  应该都是动态的绑定在程序中吧  sql把这些值取出来  然后在程序里面判断   即使能按这个思路把值取出来 也没那么简单  还是动态的

Answer 5

--建立表格
create table tmp
(
 histdate varchar2(20),
 name varchar2(10),
 type varchar2(10)
 );
--插入数据
insert into tmp
select '2011/09/01','张三','请假' from dual
union all
select '2011/09/01','李四','加班' from dual
union all
select '2011/09/06','王','加班' from dual
union all
select '2011/09/30','赵','请假' from dual;
select to_char(to_date('2012/02/26','yyyy-mm-dd'),'D')-1 from dual;
--执行语句
select 
   min(decode(xqs,0,TO_CHAR(DAYS,'mm/dd'),''))
   ||chr(10)||
   max(case when xqs=0 and type='请假' then name else '     ' end)
   ||'  '||
   max(case when xqs=0 and type='加班' then name else '     ' end)
   "星期日(请假|加班)", 
   min(decode(xqs,1,TO_CHAR(DAYS,'mm/dd'),''))
   ||chr(10)||
   max(case when xqs=1 and type='请假' then name else '     ' end)
   ||'  '||
   max(case when xqs=1 and type='加班' then name else '     ' end)
   "星期一(请假|加班)", 
   min(decode(xqs,2,TO_CHAR(DAYS,'mm/dd'),''))
   ||chr(10)||
   max(case when xqs=2 and type='请假' then name else '     ' end)
   ||'  '||
   max(case when xqs=2 and type='加班' then name else '     ' end)
   "星期二(请假|加班)", 
   min(decode(xqs,3,TO_CHAR(DAYS,'mm/dd'),''))
   ||chr(10)||
   max(case when xqs=3 and type='请假' then name else '     ' end)
   ||'  '||
   max(case when xqs=3 and type='加班' then name else '     ' end)
   "星期三(请假|加班)", 
   min(decode(xqs,4,TO_CHAR(DAYS,'mm/dd'),''))
   ||chr(10)||
   max(case when xqs=4 and type='请假' then name else '     ' end)
   ||'  '||
   max(case when xqs=4 and type='加班' then name else '     ' end)
   "星期四(请假|加班)", 
   min(decode(xqs,5,TO_CHAR(DAYS,'mm/dd'),''))
   ||chr(10)||
   max(case when xqs=5 and type='请假' then name else '     ' end)
   ||'  '||
   max(case when xqs=5 and type='加班' then name else '     ' end)
   "星期五(请假|加班)", 
   min(decode(xqs,6,TO_CHAR(DAYS,'mm/dd'),''))
   ||chr(10)||
   max(case when xqs=6 and type='请假' then name else '     ' end)
   ||'  '||
   max(case when xqs=6 and type='加班' then name else '     ' end)
   "星期六(请假|加班)"
from
 (
 select
       b.*,histdate,to_char(days,'D')-1 xqs, 
       name, type, to_number(to_char(DAYS + 7 - 1,'ww'))-to_number(to_char(trunc(DAYS,'mm') + 7 - 1 ,'ww')) zs from
 tmp A
 RIGHT JOIN
 (select 
     last_day(to_date((select min(histdate) from tmp),'yyyy-mm-dd'))-level+1 DAYS
  from dual
  connect by rownum <= to_number(to_char(last_day(to_date((select min(histdate) from tmp),'yyyy-mm-dd')),'dd'))
  ) B ON B.DAYS = TO_DATE(A.histdate,'yyyy-mm-dd')
 ) a 
  group by zs
  order by zs

Answer 6

--记录格式稍微有一些不太好看，主要不能上传图片，只有自行执行后查看了星期日(请假|加班)                   星期一(请假|加班)                  
---------------------------- ----------------------------
星期二(请假|加班)                   星期三(请假|加班)                  
---------------------------- ----------------------------
星期四(请假|加班)                   星期五(请假|加班)                  
---------------------------- ----------------------------
星期六(请假|加班)                  
----------------------------
                                                         
                                                         
                                                         
                                                         
09/01                        09/02                       
张三  李四                                                   
09/03                       
                            
                                                                                
09/04                        09/05                       
                                                         
09/06                        09/07                       
       王                                                 
09/08                        09/09                       
                                                         星期日(请假|加班)                   星期一(请假|加班)                  
---------------------------- ----------------------------
星期二(请假|加班)                   星期三(请假|加班)                  
---------------------------- ----------------------------
星期四(请假|加班)                   星期五(请假|加班)                  
---------------------------- ----------------------------
星期六(请假|加班)                  
----------------------------
09/10                       
                            
                                                                                
09/11                        09/12                       
                                                         
09/13                        09/14                       
                                                         
09/15                        09/16                       
                                                         
09/17                       
                            
                                                                                
09/18                        09/19                       
                                                         
09/20                        09/21                       星期日(请假|加班)                   星期一(请假|加班)                  
---------------------------- ----------------------------
星期二(请假|加班)                   星期三(请假|加班)                  
---------------------------- ----------------------------
星期四(请假|加班)                   星期五(请假|加班)                  
---------------------------- ----------------------------
星期六(请假|加班)                  
----------------------------
                                                         
09/22                        09/23                       
                                                         
09/24                       
                            
                                                                                
09/25                        09/26                       
                                                         
09/27                        09/28                       
                                                         
09/29                        09/30                       
                             赵                           
                            
                            
                                                                                5 rows selected.