交叉表 老问题！

Question

现有员工打卡表：punch_card_record 
字段：user_id,user_name,record_date(打卡日期 日期类型),onduty_time(上班时间 时间类型),
  offduty_time(下班时间 时间类型) 
user_id  user_name  record_date  onduty_time  offduty_time
001      张三       2009-01-02     08：55      17：21
002      李四       2009-01-02     09：00      17：40
003      王五       2009-01-02     08：58      17：19
001      张三       2009-01-03     08：47      17：23
002      李四       2009-01-03     08：40      17：44
003      王五       2009-01-03     09：03      17：29
001      张三       2009-01-04     09：19      17：28
002      李四       2009-01-04     08：50      17：10
003      王五       2009-01-04     08：54      17：26我想用语句实现
user_id  user_name   2009-01-02  2009-01-03  2009-01-04  。。等等根据我给定的起止时间依次循环。
001      张三         √   √     √    √    △   √
002      李四         √   √     √    √    √   △
003      王五         √   △     △    √    √   √9：00---17：20之外的显示√ 其他显示 △以前问过一次，没有满意答案 可能没表达清楚。

Answer 1

这个老问题是与表结构的设计有很大关系,按你这样种表结构估计查询也是很慢的.....
一个办法是用decode或case when ... then ... else ... end
另一个办法就是用存储过程+循环+临时表;我看还是第二个方法好一些

Answer 2

select user_id,
       user_name,
       decode(to_char(record_date, 'dd'),
              '01',
              decode(sign((onduty_time -
                          To_Date(to_char(record_date, 'yyyy-mm-dd') ||
                                   '09:00:00',
                                   'yyyy-mm-dd hh24:mi:ss')) * 86400),
                     -1,
                     '△',
                     decode(sign((onduty_time -
                                 To_Date(to_char(record_date, 'yyyy-mm-dd') ||
                                          '17:20:00',
                                          'yyyy-mm-dd hh24:mi:ss')) * 86400),
                            1,
                            '△','√'))) as '**-**-01'
  from punch_card_record;

Answer 3

 select user_ID,user_name,
  max(
  case  when 
  record_date=to_date('2009-01-02','yyyy-mm-dd') 
 and onduty_time >to_date('09:00','hh24:mi')
 and onduty_time <to_date('17:20','hh24:mi')
  then '△' else '√'  end ),
    max(
  case  when 
  record_date=to_date('2009-01-03','yyyy-mm-dd') 
 and onduty_time >to_date('09:00','hh24:mi')
 and onduty_time <to_date('17:20','hh24:mi')
  then '△' else '√'  end  ),
   max(
  case  when 
  record_date=to_date('2009-01-03','yyyy-mm-dd') 
 and onduty_time >to_date('09:00','hh24:mi')
 and onduty_time <to_date('17:20','hh24:mi')
  then '△' else '√'  end  )
  from punch_card_record
group by  user_ID,user_name
天数少就这样写,天数多的话就用存储过程处理,对了我上面取别名时会出现错误,所以我没用别名,其它你自己处理吧

Answer 4

create or replace Package SysBasic 
AS
  type T_cursor is ref cursor;
end SysBasic;create or replace procedure GetEmpPunch_card(
  v_begdate  varchar,
  v_enddate  varchar,
  c_cursor   OUT SysBasic.T_cursor 
)
is
  cursor c_punch_card(v_begdate,v_enddate) is
    select distinct record_date
      from punch_card_record
      where record_date between convert(date,v_begdate,'yyyy-mm-dd') end convert(date,v_enddate,'yyyy-mm-dd')
      order by record_date;
  strSql varchar2(4000);
begin
  strSql := 'select a.user_id,a.user_name ';
  for t_punch_card in c_punch_card(v_begdate,v_enddate) loop
     strSql := strSql||',sum(case when a.record_date = t_punch_card.record_date and (a.onduty_time <= 9:00 or a.offduty_time >= 17:20) then 1 else 0 end) "'||t_punch_card.record_date||'"';
  end loop;
  strSql := strSql||' from punch_card_record a where a.record_date between convert(date,v_begdate,'yyyy-mm-dd') end convert(date,v_enddate,'yyyy-mm-dd') group by a.user_id,a.user_name';  open c_cursor for strSql;end GetEmpPunch_card;
注：结果中 1代表√，0代表△

Answer 5

lz,按你的SQL,你的列是不固定的，这意味着sql的复杂度随列数的增多而变得更复杂。你可以把可变列合成以逗号隔开的一个列，然后在客户端按逗号分解分解。例如：SQL> var x date
SQL> var y number
SQL> exec :x:=to_date('2009-01-01','yyyy-mm-dd'); --开始时间
SQL> exec :y:=8; --查询8天内SELECT user_id, user_name, regexp_replace(translate(r, d, v), '[^√,△]+', '△')
FROM   (SELECT user_id,
               user_name,
               REPLACE(wmsys.wm_concat(chr(record_date - :x + 1)), ',') d,
               REPLACE(wmsys.wm_concat(CASE
                                           WHEN onduty_time <= '09:00' AND offduty_time >= '17:20' THEN
                                            '√'
                                           ELSE
                                            '△'
                                       END),',') v
        FROM   punch_card_record
        WHERE  record_date BETWEEN :x AND :x + :y + 1
        GROUP  BY user_id, user_name) a,
       (SELECT wmsys.wm_concat(chr(ROWNUM)) r FROM dual CONNECT BY ROWNUM <= :y);USER_ID USER_NAME  REGEXP_REPLACE(TRANSLATE(R,D,V
------- ---------- --------------------------------------------------------------------------------
001     张三       △,√,√,△,△,△,△,△
002     李四       △,√,√,△,△,△,△,△
003     王五       △,△,√,△,△,△,△,△如果你坚持要不定列，那么：SQL> var x date
SQL> var y number
SQL> exec :x:=to_date('2009-01-01','yyyy-mm-dd'); --开始时间
SQL> exec :y:=8; --查询8天内
SELECT user_id,
       user_name,
       decode(instr(d,regexp_substr(r,'[^,]+',1,1)),0,'△','√') r1,
       decode(instr(d,regexp_substr(r,'[^,]+',1,2)),0,'△','√') r2,
       decode(instr(d,regexp_substr(r,'[^,]+',1,3)),0,'△','√') r3,
       decode(instr(d,regexp_substr(r,'[^,]+',1,4)),0,'△','√') r4,
       decode(instr(d,regexp_substr(r,'[^,]+',1,5)),0,'△','√') r5,
       decode(instr(d,regexp_substr(r,'[^,]+',1,6)),0,'△','√') r6,
       decode(instr(d,regexp_substr(r,'[^,]+',1,7)),0,'△','√') r7,
       decode(instr(d,regexp_substr(r,'[^,]+',1,8)),0,'△','√') r8
FROM   (SELECT user_id,
               user_name,
               wmsys.wm_concat(chr(record_date - :x + 1)) d,
               wmsys.wm_concat(CASE
                                   WHEN onduty_time <= '09:00' AND offduty_time >= '17:20' THEN
                                    '√'
                                   ELSE
                                    '△'
                               END) v
        FROM   punch_card_record
        WHERE  record_date BETWEEN :x AND :x + :y +1
        GROUP  BY user_id, user_name) a,
       (SELECT wmsys.wm_concat(chr(ROWNUM)) r FROM dual CONNECT BY ROWNUM <= :y);USER_ID USER_NAME  R1 R2 R3 R4 R5 R6 R7 R8
------- ---------- -- -- -- -- -- -- -- --
001     张三       △ √  √  √  △ △ △ △
002     李四       △ √  √  √  △ △ △ △
003     王五       △ √  √  √  △ △ △ △

Answer 6

后一个SQL搞错了。SELECT user_id,
       user_name,
       decode(instr(d,regexp_substr(r,'[^,]+',1,1)),0,'△','√') r1,
       decode(instr(d,regexp_substr(r,'[^,]+',1,2)),0,'△','√') r2,
       decode(instr(d,regexp_substr(r,'[^,]+',1,3)),0,'△','√') r3,
       decode(instr(d,regexp_substr(r,'[^,]+',1,4)),0,'△','√') r4,
       decode(instr(d,regexp_substr(r,'[^,]+',1,5)),0,'△','√') r5,
       decode(instr(d,regexp_substr(r,'[^,]+',1,6)),0,'△','√') r6,
       decode(instr(d,regexp_substr(r,'[^,]+',1,7)),0,'△','√') r7,
       decode(instr(d,regexp_substr(r,'[^,]+',1,8)),0,'△','√') r8
FROM   (SELECT user_id,
               user_name,
               wmsys.wm_concat(chr(record_date - :x + 1)) d
        FROM   t
        WHERE  record_date BETWEEN :x AND :x + :y +1
        AND    onduty_time <= '09:00' AND offduty_time >= '17:20'
        GROUP  BY user_id, user_name) a,
       (SELECT wmsys.wm_concat(chr(ROWNUM)) r FROM dual CONNECT BY ROWNUM <= :y);USER_ID USER_NAME  R1 R2 R3 R4 R5 R6 R7 R8
------- ---------- -- -- -- -- -- -- -- --
001     张三       △ √  √  △ △ △ △ △
002     李四       △ √  √  △ △ △ △ △
003     王五       △ △ △ √  △ △ △ △