sql优化问题 大神可以看看吗

Question

select * from (
select soi.*,timestampdiff(year,soi.RegDate, "2015-12-31" ) as counts1,count(ii.CertificateRegNum) counts2,
(select count( DISTINCT iii.CertificateRegNum ) from InspectionInfo iii WHERE  iii.AYear between ('2015'-counts1) and '2015' and iii.CertificateRegNum = soi.CertificateRegNum)  counts3 
from SocialOrgBasicInfo   soi left join InspectionInfo ii on soi.CertificateRegNum = ii.CertificateRegNum 
where soi.CertificateRegNum not in (SELECT DISTINCT CertificateRegNum from  SocialOrgBasicInfo WHERE  AuditStatus =-1) 
and ii.AYear< "2015"  and soi.RegDate !=""  GROUP BY ii.CertificateRegNum 
) as bb where  counts2<counts1 AND 1=1  and 1=1 and 1=1 and 1=1 
现在数据多了 不知道该怎么简化了

Answer 1

代码有点乱，先帮你格式化一下：
SELECT  *
FROM    ( SELECT    soi.* ,
                    timestampdiff(year, soi.RegDate, "2015-12-31") AS counts1 ,
                    COUNT(ii.CertificateRegNum) counts2 ,
                    ( SELECT    COUNT(DISTINCT iii.CertificateRegNum)
                      FROM      InspectionInfo iii
                      WHERE     iii.AYear BETWEEN ( '2015' - counts1 ) AND '2015'
                                AND iii.CertificateRegNum = soi.CertificateRegNum
                    ) counts3
          FROM      SocialOrgBasicInfo soi
                    LEFT JOIN InspectionInfo ii ON soi.CertificateRegNum = ii.CertificateRegNum
          WHERE     soi.CertificateRegNum NOT IN ( SELECT DISTINCT
                                                            CertificateRegNum
                                                   FROM     SocialOrgBasicInfo
                                                   WHERE    AuditStatus = -1 )
                    AND ii.AYear < "2015"
                    AND soi.RegDate != ""
          GROUP BY  ii.CertificateRegNum
        ) AS bb
WHERE   counts2 < counts1
        AND 1 = 1
        AND 1 = 1
        AND 1 = 1
        AND 1 = 1