带有外部字段链接的select中的MySQL子查询

Question

我将一个旧软件(使用MS-ACCESS MDB)转换为mySQL。我有一个查询需要很长时间才能运行(实际上，我在等待5分钟后就中断了运行)，我该如何编写它？

SELECT pa_ID, pa_PRODUCT_ID, pr_ID,pr_NAME,Sum(pa_KILOS) as IN_KILOS, 
    (select sum(pl_KILOS) from POLHSH where POLHSH.pl_PRODUCT_ID = pa_PRODUCT_ID and POLHSH.pl_PARALABH_ID = pa_ID) as OUT_KILOS From PARALABH, PRODUCTS WHERE pa_company_id=1 GROUP BY pa_ID, pa_PRODUCT_ID,pr_ID, pr_NAME HAVING pa_ID=241 and pr_id=pa_PRODUCT_ID

提前感谢

Answer 1

考虑避免相关子查询，它为每一行单独运行一个SUM，并使用两个聚合查询的连接，每个聚合查询通过对字段进行分组来运行SUM一次。此外，在连接表/视图时使用explicit joins，这是当前的标准。

请将列别名和名称调整为实际值，如下所示。

SELECT t1.*, t2.OUT_KILOS
FROM
  (SELECT pa.pa_ID,
          pa.pa_PRODUCT_ID,
          pr.pr_ID,
          pr.pr_NAME,
          SUM(pa.pa_KILOS) AS IN_KILOS
   FROM PARALABH pa
   INNER JOIN PRODUCTS pr
      ON pr.pr_id = pa.pa_PRODUCT_ID
   WHERE pa.pa_company_id = 1
   GROUP BY pa.pa_ID,
            pa.pa_PRODUCT_ID,
            pr.pr_ID,
            pr.pr_NAME
   HAVING pa.pa_ID = 241
  ) AS t1

INNER JOIN
  (SELECT POLHSH.pl_PRODUCT_ID,
          POLHSH.pl_PARALABH_ID 
          SUM(pl_KILOS) As OUT_KILOS
   FROM POLHSH
   GROUP BY POLHSH.pl_PRODUCT_ID,
            POLHSH.pl_PARALABH_ID 
  ) AS t2

ON t2.pl_PRODUCT_ID = t1.pa_PRODUCT_ID
AND t2.pl_PARALABH_ID = t1.pa_ID