工作中遇到的几个SQL写法（自用）

一、如何写倒计时

select id,

       type,

       active_flag,

       message,

       to_char(start_time, 'YYYY/MM/DD HH24:MI:SS') as start_time,

       to_char(end_time, 'YYYY/MM/DD HH24:MI:SS')  as end_time,

       sysdate,

       floor((end_time-sysdate)*86400) as djs,   --倒计时，当前时间距离截止时间还有多长时间的总秒数

      floor(floor((end_time-sysdate)*86400)/3600) as hour,       --时

     floor( mod((end_time-sysdate)*86400,3600)/60) as minute,     --分

    mod((mod((end_time-sysdate)*86400,3600)) ,60) as second       --秒

  from UP_MAINTENANCE

 where type = 'pc'

1.floor（）向下取整函数（java同理）

2.mod（num1， num2）求余数，num1为被除数，num2为除数（java则是num1%num2）

3.(end_time-sysdate)*86400 两个日期相减是以天为单位如图。

乘以86400化为秒数（3600*24 = 86400）

 二、锁表怎么解决

查看所有数据库中谁被锁住了：

SELECt object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERe l.object_id = o.object_id

AND l.session_id = s.sid;

解锁表：

alter system kill session '最后一条sid,最后一条serial’;

三、结果由多列合成一列（LISTAGG函数）

SELECt LISTAGG(aaa345,chr(10)) WITHIN GROUP(ORDER BY(aaa345)) aaa345,
       LISTAGG(SUM(je),chr(10)) WITHIN GROUP(ORDER BY(aaa345)) ZJE,
       LISTAGG((case when sum(jsje)>0 then '是' else '否' end),chr(10)) WITHIN GROUP(ORDER BY(aaa345)) SFJS,
       LISTAGG(sum(jsje),chr(10)) WITHIN GROUP(ORDER BY(aaa345)) ZJSJE,
       LISTAGG(sum(wjsje),chr(10)) WITHIN GROUP(ORDER BY(aaa345)) ZWJSJE
  FROM (SELECt (SELECT AAA103 FROM AA10_TABLE WHERe AAA100 ='AAA036' AND AAA102 = HNSI_BDDYYY.ic61.AAA345 )aaa345, 
        aae014, SUM(aae019) je,
          (CASE
         WHEN aae014 = 1 THEN
          sum(aae019)
         ELSE
          0
       END) jsje,
       (CASE
         WHEN aae014 = 2 THEN
          sum(aae019)
         ELSE
          0
       END) wjsje
          FROM HNSI_BDDYYY.ic61
         WHERe aaz257 = $aaz257
           AND aae014 IN ('1', '2')
         GROUP BY aaa345, aae014)
 GROUP BY aaa345
 ;

效果图：