达梦常用的sql查询语句

 1：查总数，求和，平均值，最大值，最小值

总数：
select count(basePersonId) as totalcount from  "INNOSOFT"."BaseUser" 
UNION
求和：
select sum(basePersonId) as sumvalue from "INNOSOFT"."BaseUser"
平均：
select avg(basePersonId) as avgvalue from "INNOSOFT"."BaseUser"
最大：
select max(basePersonId) as maxvalue from "INNOSOFT"."BaseUser"
最小：
select min(basePersonId) as minvalue from "INNOSOFT"."BaseUser"

union all /except all/intersect all

select id,name,basePersonId,userType  from  "INNOSOFT"."BaseUser"  
union all
select id,name,baseUserId,id  from  "INNOSOFT"."BasePerson"
       
select id,name,basePersonId,userType  from  "INNOSOFT"."BaseUser"  
except all
select id,name,baseUserId,id  from  "INNOSOFT"."BasePerson"     

left join/right join/full(cross)cross join

select u.id,u.name,u.basePersonId,p.id,p.name from  "INNOSOFT"."BaseUser"  u 
left join "INNOSOFT"."BasePerson" p
on u.basePersonId = p.id

select u.id,u.name,u.basePersonId,p.id,p.name from  "INNOSOFT"."BaseUser"  u 
right join "INNOSOFT"."BasePerson" p
on u.basePersonId = p.id

select u.id,u.name,u.basePersonId,p.id,p.name from  "INNOSOFT"."BaseUser"  u 
full join "INNOSOFT"."BasePerson" p
on u.basePersonId = p.id

not in/in

select count(1) as num, eventId   from  "INNOSISP"."LogAlarm" where "eventId" not in ('283402604124479488', '5', '301', '303', '280626153444777984') group by eventId
select count(1) as num, eventId   from  "INNOSISP"."LogAlarm" where "eventId" in ('283402604124479488', '5', '301', '303', '280626153444777984') group by eventId

两张关联表，删除主表中已经在副表中没有的信息

       
delete from "WORKFLOW"."WfTaskOper" oper where not exists ( select * from "WORKFLOW"."WorkFlowTest" test where oper.id=test.personId )

四表联查问题

select a.id,a.wfTaskId,a.taskName,a.taskKey,a.wfInstanceId,a.wfDeploymentId,b.id as instanceId,c.name,d.businessData from "WORKFLOW"."WfTaskLog" as a 
left inner join  "WORKFLOW"."WfInstance" as b  on a.wfInstanceId =b.id 
right inner join "WORKFLOW"."WfDeployment" as c on a.wfDeploymentId=c.id 
inner join "WORKFLOW"."WfInstanceData" as d on a.wfInstanceId=d.wfInstanceId where a.id = '1621420897225281536'

日程安排提前五min提醒/超时30min提醒 

select * from "WORKFLOW"."WfTask"  where datediff(minute,createDate,getdate())>5 and datediff(minute,createDate,getdate())<=40
select * from "WORKFLOW"."WfTask"  where datediff(d,createDate,'2023-02-04')<=0 or datediff(d,createDate,'2023-03-27')>=0

top 5

select top 5 * from "WORKFLOW"."WfTask" 
select count(1) as num,violator from "INNOSISP"."LogAlarm" group by "violator" order by num desc limit 10  

随机取6条数据

select top 6 * from "WORKFLOW"."WfTask" order by newid()

查询时间段内数据

select * from "INNOSISP"."LogAlarm" where  ("beginTime" between '2023-03-11 00:00:00' and '2023-03-13 00:00:00')