EAS数据库SQL相关（自用笔记）

web端访问数据库

CSDN

--查询表分录分组倒序
SELECT count(t2.FPARENTID) 条数,t2.FparentId FROM T_AP_OtherBill t1
left join T_AP_OTHERBILLENTRY t2 on t1.fid = t2.fparentid
where t1.FCOMPANYID = 'uB/h0hkcQkyfkdUSFzzdUsznrtQ='
--and  rownum <10000
group by t2.FPARENTID 
order by 条数
desc

SQL相关

oracle数据库查询多条以逗号隔开

/*dialect*/ select  

listagg(CFENDORSENO,',') within GROUP (order by CFENDORSENO)  

 from CT_CAS_endorseEntry where FPARENTID = '47pDcZDtRXqQ93Mr0OdFzkAoToE='

批量插入

INSERT INTO T_AR_ARAPRELATIONENTRY
SELECT 'id1','aaa','aaa','aaa','aaa',now() union
SELECT 'id2','aaa','aaa','aaa','aaa',now() union
SELECT 'id3','aaa','aaa','aaa','aaa',now() 

时间相关

INSERT INTO T_AR_ARAPRELATIONENTRY (Fid,FDATE ) VALUES ('id2',now())

INSERT INTO T_BAS_MultiApprove (Fid,FDATE ) VALUES ('id2',convert(datetime,'2020-09-18 08:32:16'))


//时间比较
and   a.FLASTUPDATETIME >=Convert(datetime,'2017-03-22')

获取指定条数数据

where rownum<10;--获取小于10条数据

ControllerBean

删除

com.kingdee.eas.util.app.DbUtil.execute(ctx, sql);

查询

IRowSet rowset =DbUtil.executeQuery(ctx, sql);
while(rowset.next()) {
    String reportId = rowset.getString("reportId");
}