如何在MYSQL中将有条件的内部查询转换为左连接？

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中将有条件的内部查询转换为左连接？

我正在尝试将此子查询转换为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

Answer 2

我会改用EXISTS

SELECT *
FROM CC INNER JOIN
     App
     ON CC.id = App.id 
WHERE CC.counter > 1000 AND
      ( (CC.key1 = App.key1 AND
         NOT EXISTS (SELECT 1
                     FROM Service
                     WHERE App.No = Service.No
                    ) OR
        (CC.key1 = App.key1 AND
         NOT EXISTS (SELECT 1
                     FROM Service
                     WHERE App.NoCo = Service.No AND
                           App.NoCo <> ''
                    )
         )
        );

为了提高性能，您需要一个关于Service(No)和CC(counter, id)的索引。

您必须小心使用LEFT JOIN，因为您的条件具有OR。这可能会无意中增加行数。