菜鸟问题，求Sql语句。。。

Question

select ID ,RecordTime ,(case 
when TO_NUMBER(TO_CHAR(recodetime,'HH24'))<12 and TO_NUMBER(TO_CHAR(recodetime,'HH24'))>=8 then 'AM'
when TO_NUMBER(TO_CHAR(recodetime,'HH24'))<17 and TO_NUMBER(TO_CHAR(recodetime,'HH24'))>=13 then 'PM'
else null end) AMPM 
from RecordList

Answer 1

sbaz(万神渡劫)：非常感谢你上次帮我解决问题，不过不行啊，我现在用的是8i，没有case语句啊

Answer 2

SELECT ID ,RecordTime, 
      DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS'),'080000'), -1,NULL,
       DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS'),'120000'),
               1,DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS'),'130000'),-1,NULL,
                        DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS'),'170000'),1,NULL,'PM')
                       ),'AM'
             )
      ) AS AMPM
FROM RecordList

Answer 3

不行啊，提示在第二行Sign那有错误，说未定义变量今天下班，明天继续上来，解决后马上加分。

Answer 4

与其搞那么多CASE,还不如简单点搞,多写点代码:
select ID ,RecordTime ,'AM' AMPM
from RecordList
WHERE TO_NUMBER(TO_CHAR(recodetime,'HH24'))<=12 and TO_NUMBER(TO_CHAR(recodetime,'HH24'))>=8
union 
select ID ,RecordTime ,'PM' AMPM
from RecordList
WHERE TO_NUMBER(TO_CHAR(recodetime,'HH24'))<17 and TO_NUMBER(TO_CHAR(recodetime,'HH24'))>=13
union
select ID ,RecordTime ,NULL AMPM 
from RecordList
WHERE TO_NUMBER(TO_CHAR(recodetime,'HH24'))>=17 or TO_NUMBER(TO_CHAR(recodetime,'HH24'))<8 

Answer 5

SELECT ID ,RecordTime, 
      DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS')-'080000'), -1,NULL,
       DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS')-'120000'),
               1,DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS')-'130000'),-1,NULL,
                        DECODE(SIGN(TO_CHAR(RecordTime,'HH24MISS')-'170000'),1,NULL,'PM')
                       ),'AM'
             )
      ) AS AMPM
FROM RecordList

Answer 6

这下解决问题了结贴sbaz(万神渡劫)：
这个方法我想过，如果解不出来我就打算用这个方法了，后备方案:-)