MySQL 按照次数去除A结果集中B结果集重复数据

最近做需求的时候遇到一种需要特殊处理的数据。就是同一个项目编号可能会被多次提交再多次驳回，而且会被记录在表中，两种状态值。需求要求：查出来提交且没有被驳回的数据。这样简单的DISTINCT,Group By去重并不能满足。

这里使用一种思路来解决（不一定为最优解）：增加一列次数统计，然后做减法

测试库数据如下

 结果集A如下：

结果集B如下：

假设state=1是提交  state= 2是驳回 

按照 驳回次数去去掉提交中的数据，即A中去掉B中的1个P3，1个P4 ，应为（p1,p2,p3）而不是（p1,p2）

先做一下次数统计

SELECT passNum,passProjectId,ifnull(rejectNum,0) as rejectNum,rejectProjectId from 
(SELECT COUNT(1) as passNum,project_id as passProjectId from pass_test where state = 1 GROUP BY project_id) t1
LEFT JOIN 
(SELECT COUNT(1) as rejectNum,project_id as rejectProjectId from pass_test where state = 2 GROUP BY project_id) t2 
on t1.passProjectId = t2.rejectProjectId

 将结果集做为子查询 ，过滤出来 两个次数相减为1 的数据

SELECT passProjectId,(passNum-rejectNum) AS diffNum from (
SELECT passNum,passProjectId,ifnull(rejectNum,0) as rejectNum,rejectProjectId from 
(SELECT COUNT(1) as passNum,project_id as passProjectId from pass_test where state = 1 GROUP BY project_id) t1
LEFT JOIN 
(SELECT COUNT(1) as rejectNum,project_id as rejectProjectId from pass_test where state = 2 GROUP BY project_id) t2 
on t1.passProjectId = t2.rejectProjectId
) p1 
where (passNum-rejectNum) =1