一、如何写倒计时
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
;
效果图: