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')