sql总结

select count(1) from tbm_hk_data where edate>'2023-09-30' 

select * from tbm_hk_data WHERE jobno='1400073'  

--UPDATE tbm_hk_data SET jobno='0023518' WHERE jobno='1400073'



--delete from tbm_hk_data where orgname<> '0001A2100000003EC903'

select * from 
tbm_hk_data where orgname='0001A2100000003EC903' and edate='2024-02-24' 


--create table tbm_hk_data_h as select * from tbm_hk_data where orgname<> '0001A2100000003EC903'

select * from tbm_hk_data_h

--alter table tbm_hk_data rename to tbm_hk_data2024

--alter table tbm_hk_data_h rename to tbm_hk_data



select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;


--alter system kill session '2438,30005';


select name from bd_defdoc where pk_defdoclist = '1001A2100000004GT2OZ'

SELECT pk_defdoclist  FROM bd_defdoc GROUP BY pk_defdoclist


select b.pk_psndoc, b.code clerkcode, b.id,a.pk_group,a.pk_org,b.name from nc65.tbm_psndoc a
		    left join nc65.bd_psndoc b on a.pk_psndoc=b.pk_psndoc
		    where b.dr = 0
        
        
        
select * from sm_user where dr =0 and user_code='0114059'


select s.code, r.begintime, r.endtime from bd_shift s inner join bd_rt r on s.pk_shift = r.pk_shift
 where s.dr = 0 and r.dr = 0 AND r.begintime<>'12:00:00' AND  s.code IN ('123',
'101',
'102',
'105',
'111',
'112'
)
GROUP BY s.code, r.begintime, r.endtime


select im.pk_importdata, im.calendardate, im.calendartime, im.timecardid, im.pk_psndoc, im.pk_group, im.pk_org, im.creator
from tbm_importdata im where im.dr = 0 and im.pk_psndoc IN(
select i.pk_psndoc n from tbm_importdata i where i.dr = 0 and i.pk_psndoc in ( 
select c.pk_psndoc from tbm_psncalendar c inner join bd_shift s on c.pk_shift = s.pk_shift 
 where c.dr = 0 and s.dr = 0 and s.code = '101')
and i.calendartime between '2024-02-19 11:30:00' and '2024-02-19 13:00:00'
group by i.pk_psndoc having count(*) =1)
and im.calendartime between  '2024-02-19 11:30:00' and '2024-02-19 13:00:00'

SELECT * FROM tbm_importdata


select b.pk_psndoc, b.code clerkcode, b.id, b.pk_group, b.pk_org,b.name,c.code,a.calendar,c.timebegintime time1,c.begintime time2,d.begintime time3,d.endtime time4,c.endtime time5,c.timeendtime time6
 from tbm_psncalendar a
left join bd_psndoc  b
 on a.pk_psndoc=b.pk_psndoc
   left join bd_shift c
    on a.pk_shift=c.pk_shift
    left join bd_rt d
    on d.pk_shift=c.pk_shift
    where b.dr = 0
AND a.calendar='2024-02-07'