运维工作积累——sql及plsql使用

通过plsql对dmp文件进行导入和导出

plsql导出dmp

 

1、如何sql语句中的列将列变成行（将图一变成图二）

去除order by 对此字段分类，加上  sum(case when WORKSTATE='1'   then 1 else 0 end)   数值1，只需替换when后面条件即可

 

select 
centno 编号1 ,WORKSTATE，
count(WORKDATE) 总数量  
from   CD_WORKDATE
where WORKDATE  between to_date('20200101','yyyymmdd') and to_date('20201231','yyyymmdd')  
group by centno,WORKSTATE
order by centno ASC

 

select 
centno 编号1 ,
count(WORKDATE) 总数量,
sum(case when WORKSTATE='1'   then 1 else 0 end) 数值1，
sum(case when WORKSTATE='0'   then 1 else 0 end) 数值0
from   CD_WORKDATE
where WORKDATE  between to_date('20200101','yyyymmdd') and to_date('20201231','yyyymmdd')  
group by centno
order by centno ASC