mysql - 需要查询优化，因为子查询太多，子查询依赖于 where 条件

Question

我正在为生产报告编写代码。我写了这个查询

SELECT
    P.*,
    (
    SELECT
        COUNT(id) AS cnt
    FROM
        bales
    WHERE
        create_date < '2019-11-01' AND product_id = P.id AND(TYPE = 'bale' OR TYPE = 'bag')
) AS before_prod,
(
    SELECT
        COUNT(id) AS cnt
    FROM
        bales
    WHERE
        (
            dispatched = '0' OR disp_bunch = '0'
        ) AND dispatch_date < '2019-11-01' AND product_id = P.id AND(TYPE = 'bale' OR TYPE = 'bag')
) AS before_dispatched,
(
    SELECT
        COUNT(id) AS cnt
    FROM
        bales
    WHERE
        create_date BETWEEN '2019-11-01' AND '2019-11-06' AND product_id = P.id AND(TYPE = 'bale' OR TYPE = 'bag')
) AS production,
(
    SELECT
        COUNT(id) AS cnt
    FROM
        bales
    WHERE
        (
            dispatched = '0' OR disp_bunch = '0'
        ) AND dispatch_date BETWEEN '2019-11-01' AND '2019-11-06' AND product_id = P.id AND(TYPE = 'bale' OR TYPE = 'bag')
) AS production_dispatched,
C.name AS category_name
FROM
    products P
INNER JOIN category C ON
    C.id = P.category

此查询有效，但由于我在所有表中的记录太多，因此需要花费太多时间。另外，我只需要记录 before_prod, before_dispatched, production, production_dispatched 所有这些子查询结果都应该大于 0。

我尝试使用 having 子句，但它也花费了太多时间。

我也试过 php for loop，* LOGIC: 首先所有的产品都比在 for loop 中产生。但速度要慢得多。*

如何优化我的查询？

Answer 1

您可以改用join 和select case 来汇总符合条件的数据。

select p.*, t.*
from products p
inner join (
    select t2.id, sum(case when create_date < '2019-11-01' then 1 else 0 end) as before_prod
        , sum(case when (dispatched = '0' or disp_bunch = '0') and  create_date < '2019-11-01' then 1 else 0 end) as before_dispatched
        , sum(case when create_date between '2019-11-01' and '2019-11-06' then 1 else 0 end) as production
        , sum(case when (dispatched = '0' or disp_bunch = '0') and create_date between '2019-11-01' and '2019-11-06' then 1 else 0 end) as production_dispatched
    from bales t1
    inner join product t2 on t2.id= t1.product_id 
    inner join category t3 on t3.id = t2.category
    where t1.TYPE in ('bale', 'bag')
    group by t2.id) t
on t.id = p.id