postgresql_删除表里超过数量的某些数据

一个企业下有多个test，这些test之间不会有时间上的交集，一个test只属于一个企业。

一个test下有多个report，reports表保存所有企业的report。

现在需要检索所有企业的一些report进行删除，检索逻辑如下：

1. report.status=1的report不能删除
2. 每个test里最新的report不能删除
3. 每个企业删除至剩下99个report(如果不能删除的report数量>=99，只把能删除的全部删除)
4. 企业下先删除旧的test(test_pk越小test越旧)，test下先删除旧的report

select r1,flg,r2,report_pk from				--1
	(select row_number() over(partition by company_pk) as r1,* from			--2
		(select (case when t1.r2=1 or t1.status=1 then false else true end) flg,* from 		--3
			(select row_number() over(partition by test_pk order by create_time desc) as r2,* from reports) t1 		--4
		order by company_pk,flg,test_pk desc,create_time desc) t2
	) t3 
where r1>99 and flg=true order by report_pk
           

• sql4按test分组，按report降序排序，r2记录每个report的顺位
• sql3标记r2=1(每个test下最新的report)和status=1的report的flg=false表示不能删除，并按企业,flg,test,report排序
• sql3的顺序就是删除的优先顺序的逆序，sql2对每个企业report重新分配序号
• sql1取99条以后且能删除的report。
• select r1,flg,r2,report_pk和order by report_pk是为了观察结果，与逻辑无关

修改逻辑2： 每个test里最新的status=2的report不能删除

select r1,flg,r2,report_pk,* from			--1
	(select row_number() over(partition by company_pk) as r1,* from        			--2
		(select (case when (t1.r2=1 and t1.status=2) or t1.status=1 then false else true end) flg,*  from 			--3
			(select row_number() over(partition by test_pk,status order by create_time desc) as r2,* from reports) t1 		--4
        order by company_pk,flg,test_pk desc,create_time desc) t2
	) t3
where r1>0 and flg=true order by report_pk		
           

• sql4按test和status分组

• sql3的t1.r2=1 and t1.status=2标记status=2的组里最新的数据不能删除

  其他逻辑同上