前提条件,使用的Mysql数据库,
表名称:table01 表
表结构如下:
id ,resid, name ,dataid,public—status, status
id,合同id, 名字, 审批id , 数据端:0/平台:1,审批状态(1同意/0拒绝)
表数据示例:
1,10010,张三,10086, 0, 1 //数据方
2,10010,张三,1008611,0,1 //数据方
3, 10010,张三, ' ', 1,1 //平台方
求,只有数据端审批都同意的合同,才能查到平台的数据
我这里写了一个sql,
SELECT
*
FROM
table01 a
INNER JOIN (
SELECT
t1.resid,
t1.num1
FROM
( SELECT resid, COUNT( 1 ) AS num1 FROM table01 WHERE public— STATUS = 0 AND STATUS = 1 GROUP BY resid ) t1
INNER JOIN ( SELECT resid, COUNT( 1 ) AS num2 FROM table01 WHERE public— STATUS = 0 GROUP BY resid ) t2 ON t1.resid = t2.resid
AND t1.num1 = t2.num2
) b
ON a.resid = b.resid