mysql select count, left join, 다중 select, 서브쿼리, Group by, SELECT 후 DELETE

[목적]

등록된 회선 수와 해지 상태의 회선 수가 일치하는 경우 데이터 삭제

 

SELECT D.CompId, D.Cntentry
FROM (SELECT D.CompId, count(D.EntryId) as CntEntry
      FROM BP_PRODENTRY D
      GROUP BY D.CompId ) D
LEFT JOIN (SELECT C.CompId, COUNT(case when C.ProgState=4 then 1 end) as status
           FROM BP_PRODENTRY C
           GROUP BY C.CompId
          ) as C
ON D.CompId = C.CompId
WHERE C.status >= D.Cntentry
GROUP BY D.CompId

 

 

DELETE
FROM BP_PRODENTRY
WHERE CompId IN (
                    SELECT D.CompId
                    FROM (SELECT D.CompId, count(D.EntryId) as CntEntry
                          FROM BP_PRODENTRY D
                          GROUP BY D.CompId ) D
                    LEFT JOIN (SELECT C.CompId, COUNT(case when C.ProgState=4 then 1 end) as status
                               FROM BP_PRODENTRY C
                               GROUP BY C.CompId
                              ) as C
                    ON D.CompId = C.CompId
                    WHERE C.status >= D.Cntentry
                    GROUP BY D.CompId
                    )