特殊sql记录

获取时间最新的一条数据

select applyNo, MAX( createdAt) as time  from recall_record where applyNo in(select applyNo from recall_case WHERE updatedAt > '2022-11-29 22:00:00' and updatedAt <'2022-11-29 22:05:00') GROUP BY applyNo

行转列

select sum(decode(name,'action',num)) action_,sum(decode(name,'alarm', num)) alarm,sum(decode(name,'di',num)) di,sum(decode(name,'list',num)) list from
(
select
'action' name,
count(1) num
from
SG_RPAM_PRO_ACTION
union all
select
'alarm' name,
count(1) num
from
SG_RPAM_PRO_ALARM
union all
select
'di' name,
count(1) num
from
SG_RPAM_PRO_DI
union all select 'list' name,count(1) num from SG_RPAM_PRO_RECORD_LIST)

按天检索数据无数据补0

select
t1.date_ref,
case when t2.num is null then 0 else t2.num end as num
from (select to_char(to_date('2017-11-09', 'yyyy-mm-dd')+level-1, 'yyyy-mm-dd') date_ref from sysdba.sysdual connect by level<8) as t1
left join
(
select
count(1) num,
to_char(to_date(record_time, 'yyyy-mm-dd'),'yyyy-mm-dd') date_act
from
SG_RPAM_PRO_RECORD_LIST
where
record_time between '2017-11-09' and '2017-11-15'
group by
to_char(to_date(record_time, 'yyyy-mm-dd'),'yyyy-mm-dd')
) as t2
on
t1.date_ref=t2.date_act

查看数据库锁表情况

select * from information_schema.innodb_trx