珩行天下的个人网站

select CAST(t.Id AS CHAR(19)) as tid, t.* from MonitorTask t where t.name like '%开散装油证明%'

散装油任务Id = `1801833127413444608`

select CAST(s.Id AS CHAR(19)) as tid, s.* from MonitorRecord s where s.MonitorTaskId = 1859829378117562368 order by s.CreatedAt desc limit 1000

select CAST(l.CurrentRegionId AS CHAR(19)) as CurrentRegionId, CAST(l.NextRegionId AS CHAR(19)) as NextRegionId, CAST(l.CurrentRoleId AS CHAR(19)) as CurrentRoleId, CAST(l.NextRoleId AS CHAR(19)) as NextRoleId from MonitorRecordLog l where l.MonitorRecordId = 1867147619353260032

select count(*) from MonitorRecordReply

加油站角色Id 1801831060200361984

select CAST(r.Id AS CHAR(19)) as pp, r.* from Role r where r.Code like '%jyz%'

运维管理角色 1739469981923581952

select CAST(r.Id AS CHAR(19)) as pp, r.* from Role r where r.Code like '%root%'

审核测试角色 1825826628824207360

select CAST(r.Id AS CHAR(19)) as pp, r.* from Role r where r.Code ='shcsjs'

来凤县区域Id 1739469963384758272

select * from Region w where w.Id = 1739469963384758272

三坝村区域Id 1822103466693234688

select CAST(w.Id AS CHAR(19)) as pp, w.* from Region w where w.Name like '%三坝村村民委员会%'

散装油老任务的Id=1801833127413444608

散装油新任务的Id = 1859829378117562368

select CAST(t.Id AS CHAR(19)) as tid, t.* from MonitorTask t where t.Id = 1859829378117562368

select * from MonitorRecord r where r.MonitorTaskId = 1859829378117562368

将历史数据修改为已删除

update MonitorRecord set IsDeleted = 1 where MonitorTaskId = 1859829378117562368 ---1801833127413444608

区域Id 1822102519103492096 蓝河村村民委员会

select * from Region where Id = 1822102519103492096

批量插入填报记录数据

INSERT INTO MonitorRecord ( Id ,MonitorTaskId ,WriteStatus ,RegionId ,UserId ,IsDeleted ,CreatedAt ,CreatedBy ,UpdatedAt ,UpdatedBy ,Remark ,BusinessFormId ,OpenId ,IsAnonymous ) SELECT Id+1 ,1859829378117562368 ,WriteStatus ,RegionId ,UserId ,IsDeleted ,CreatedAt ,CreatedBy ,UpdatedAt ,UpdatedBy ,Remark ,BusinessFormId ,OpenId ,IsAnonymous FROM MonitorRecord WHERE MonitorTaskId = 1801833127413444608

批量插入填报回复记录数据

查找到所有的加油站角色，区域审核数据 select * from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272

将加油站审核数据进行批量修改（确定加油站审核角色） update MonitorRecordLog set WriteStatus = 3, CurrentRoleId = 1739469981923581952, BusinessName = '加油审核' where CurrentRegionId = 1739469963384758272

查看所有非加油站角色数据，村委审核数据

select * from MonitorRecordLog l where l.CurrentRegionId != 1739469963384758272

update MonitorRecordLog set WriteStatus = 5, CurrentRoleId = 1825826628824207360, BusinessName = '村委审核' where CurrentRegionId != 1739469963384758272

INSERT INTO datareport_1859829378750902272 ( id, taskid, c1, c2, c3, c4, createby, updateby, createtime, updatetime ) SELECT id, taskid, c1, c2, c3, c4, createby, updateby, createtime, updatetime FROM datareport_1801833127438610432

修改其中taskid

update MonitorTask set Id = 1859829378117562368 where Id = 1859829378117562368

需要继续处理的（先查询再修改）

update MonitorRecord set MonitorTaskId = 1859829378117562368 where MonitorTaskId = 1859829378117562369

1月4日处理数据逻辑

select * from MonitorTask t where t.Id = 1859829378117562368

select * from Region w where w.Id = 1739469963384758272

第一种情况

1、 5677条记录有加油站的审核记录(但存在一些记录是被删除的)

select * from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272

##1、将这写加油站审核记录的数据进行处理(5677条记录) update MonitorRecordLog set WriteStatus = 3, IsHistory = 0, CurrentRoleId = 1739469981923581952, BusinessName = '加油审核' where CurrentRegionId = 1739469963384758272

##1、修改5677条的状态（MonitorRecord） select * from MonitorRecord r where r.Id in (select l.MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272)

##1、修改成功受影像记录 5471 update MonitorRecord set WriteStatus = 3 where Id in (select l.MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272)

##1、再将村委记录进行修改(6128) select * from MonitorRecordLog l where l.MonitorRecordId in (select l.MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272) and l.CurrentRegionId !=1739469963384758272

order by l.MonitorRecordId desc;

1、村委记录 IsHistory =1

1、修改村委记录是6128条

update MonitorRecordLog set WriteStatus = 5, IsHistory=1, CurrentRoleId = 1825826628824207360, BusinessName = '村委审核', NextRoleId=1739469981923581952, NextRegionId=1739469963384758272 where MonitorRecordId in (select l.MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272) and CurrentRegionId !=1739469963384758272

2、第二种情况只有村委上报记录的

select * from MonitorRecordLog l where l.MonitorRecordId not in (select l.MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272)

select * from Role where Id = 1825826628824207360

update MonitorRecordLog set WriteStatus = 5, IsHistory=0, CurrentRoleId = 1825826628824207360, BusinessName = '村委审核', NextRoleId=1739469981923581952, NextRegionId=1739469963384758272 where MonitorRecordId not in (select l.MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272)

修改MonitorRecord记录状态

select * from MonitorRecord r where r.Id not in (select l.MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272) and r.Id in (select MonitorRecordId from MonitorRecordLog l)
and r.MonitorTaskId = 1859829378117562368

select r.Id from MonitorRecord r inner join MonitorRecordLog l on r.Id = l.MonitorRecordId where r.MonitorTaskId = 1859829378117562368 and not exists ( select 1 from MonitorRecordLog ll where ll.MonitorRecordId = r.Id and ll.CurrentRegionId = 1739469963384758272 )

update MonitorRecord r set r.WriteStatus = 5 where r.Id in ( select r.Id from MonitorRecord r inner join MonitorRecordLog l on r.Id = l.MonitorRecordId where r.MonitorTaskId = 1859829378117562368 and not exists ( select 1 from MonitorRecordLog ll where ll.MonitorRecordId = r.Id and ll.CurrentRegionId = 1739469963384758272 ) )

第三种情况只需要插入村民的提交信息

select * from MonitorRecord r where r.RegionId =1859829378117562368 order by CreatedAt desc;

select r.name,mr.RegionId,mr.* FROM MonitorRecord mr inner join Region r on r.Id = mr.RegionId where Id not in (select l.MonitorRecordId from MonitorRecordLog l ) and MonitorTaskId = 1859829378117562368

order by RegionId desc group by RegionId HAVING count(*)>1

select regionId FROM MonitorRecord where Id not in (select l.MonitorRecordId from MonitorRecordLog l ) and MonitorTaskId = 1859829378117562368 group by regionId having count(*)>3

select CAST(l.MonitorRecordId as CHAR(19)) TID from MonitorRecordLog l where l.MonitorRecordId not in ( select Id from MonitorRecord l where l.Id in (select MonitorRecordId from MonitorRecordLog l where l.CurrentRegionId = 1739469963384758272) and l.MonitorTaskId = 1859829378117562368 ) and l.CurrentRegionId = 1739469963384758272

select * from MonitorRecord r where r.Id in (1829007816653041665)

select * from MonitorRecordLog l where l.MonitorRecordId = 1829007816653041665

select CAST(u.Id as CHAR(19)) TID from User u where u.RealName='胡秀付'

select CAST(r.Id as CHAR(19)) TID, r.* from MonitorRecord r where r.UserId = 1808697396352389120

select * from MonitorRecordLog l where l.MonitorRecordId in (1857240272778850305)

最后一种修改没有提交记录的

select r.name,mr.RegionId FROM MonitorRecord mr inner join Region r on r.Id = mr.RegionId where mr.MonitorTaskId = 1859829378117562368

select CAST(mr.RegionId as CHAR(19)) from MonitorRecord mr where mr.Id not in (select l.MonitorRecordId from MonitorRecordLog l) and mr.MonitorTaskId = 1859829378117562368 and mr.WriteStatus = 99 order by mr.RegionId desc;

INSERT INTO MonitorRecordLog ( Id ,MonitorRecordId ,IsHistory ,WriteStatus ,IsDeleted ,CreatedAt ,CreatedBy ,UpdatedAt ,UpdatedBy ,Remark ,CurrentRegionId ,CurrentRoleId ,NextRegionId ,NextRoleId ,AuditContent ,AuditMediaIds ,BusinessName ) SELECT Id ,Id , 0 , 2 ,IsDeleted ,CreatedAt ,CreatedBy ,UpdatedAt ,UpdatedBy ,Remark ,RegionId ,1739469981923581954 ,RegionId ,1825826628824207360 ,'' ,'' ,'村委审核' from MonitorRecord mr where mr.Id not in (select l.MonitorRecordId from MonitorRecordLog l) and mr.MonitorTaskId = 1859829378117562368 and mr.WriteStatus = 99

update MonitorRecordLog set BusinessName = '村委审核' where BusinessName is null

select * from MonitorRecordLog s where s.BusinessName is null

select * from MonitorRecord mr where mr.MonitorTaskId = 1859829378117562368 and mr.WriteStatus = 99

update MonitorRecord mr set mr.WriteStatus = 2 where mr.MonitorTaskId = 1859829378117562368 and mr.WriteStatus = 99

select CAST(r.Id as CHAR(19)) TID from Region r where r.Id = 1739469963384758272

select CAST(r.ParentId as CHAR(19)) from Region r where r.Id = 1822105194851340288

select * from Region r where r.Id in(1822105194851340288,1742464896978915328)

select * from Role r where r.Id in(1825826628824207360,1739469981923581954)

select CAST(r.Id as CHAR(19)) TID from Role r where r.Name like '%村民%'

散装油 任务Id = 1801833127413444608 ​

加油站角色Id 1801831060200361984 ​

运维管理角色 1739469981923581952 ​

审核测试角色 1825826628824207360 ​

来凤县区域Id 1739469963384758272 ​

三坝村区域Id 1822103466693234688 ​

散装油老任务的Id=1801833127413444608 ​

散装油新任务的Id = 1859829378117562368 ​

​

将历史数据修改为已删除 ​

区域Id 1822102519103492096 蓝河村村民委员会 ​

批量插入填报记录数据 ​

批量插入填报回复记录数据 ​

查看所有非加油站角色数据，村委审核数据 ​

需要继续处理的（先查询再修改） ​

第一种情况 ​

1、 5677条记录有加油站的审核记录(但存在一些记录是被删除的) ​

1、村委记录 IsHistory =1 ​

1、修改村委记录是6128条 ​

2、第二种情况只有村委上报记录的 ​

修改MonitorRecord记录 状态 ​

第三种情况 只需要插入村民的提交信息 ​

最后一种修改没有提交记录的 ​