mysql - 如何在 MYSQL 中将条件内部查询转换为 LEFT JOIN？

Question

我有这样的问题

SELECT * FROM CC 
INNER JOIN App (CC.id = App.id)   
WHERE CC.counter >1000
AND 0 = IF(CC.key1 = App.key1,
                    (SELECT COUNT(*)
                        FROM Service
                        WHERE App.No = Service.No
                     )
                    ),
                    (SELECT COUNT(*)
                        FROM Service
                        WHERE App.NoCo = Service.No
                        AND App.NoCo != ''
                      )
                    )
                )

如何在 MYSQL 中将条件内部查询转换为 LEFT JOIN？

我正在尝试将此子查询转换为连接子句以获得更好的性能

Answer 1

试试这个:

SELECT * FROM CC 
INNER JOIN App (CC.id = App.id)   
LEFT JOIN(SELECT s.no,count(*) as cnt_1,sum(s.no <> '') as cnt_2
          FROM Service s
          GROUP BY s.no) t
 ON(0 = CASE WHEN CC.key1 = App.key1 THEN t.cnt_1 ELSE t.cnt_2 END)
WHERE CC.counter >1000