我想优化以下查询。我注意到，通过在LEFT-JOIN子句中删除billing_payment_applied的子查询，可以大大减少时间。我的问题是是否有可能在没有这个子查询的情况下以某种方式重写它。

SELECT a, b, c, ...
  FROM billing_invoice_item 
  LEFT JOIN billing_statement
  LEFT JOIN 
    (SELECT SUM(x), invoice_id 
       FROM billing_payment_applied
       WHERE invoice_id != 0  AND (apply_date <= now())
       GROUP BY invoice_id) AS BAL
  WHERE [...]

在我看来，如果我们能以某种方式在SUM（）上设置一个条件，这是可能的。
上面的查询所做的是为以前收集的每条记录获取发票的SUM（）。我把它看作是一个函数调用。我们不需要对每个记录进行各种函数调用，而是获取函数调用所需的所有数据，然后执行SUM（）。我需要能够识别哪些值来自billing_payment_applied，哪些不来自billing_payment_applied，但这是可能的，因为当没有找到记录时，LEFT JOIN将产生NULL值。另外，SUM（）需要能够仅对来自billing_payment_applied的数据求和，但这似乎也可以通过CASE-WHEN语句实现。
所以，我写了

SELECT SUM(CASE WHEN BAL.id is NULL THEN 0 ELSE BAL.amount), ...
  FROM billing_invoice_item 
  LEFT JOIN billing_statement
  LEFT JOIN `billing_payments_applied` bpa on 
            bpa.invoice_id = stm.id 
        AND bpa.invoice_id != 0 
        AND (`apply_date` IS NOT NULL AND `apply_date` <= now())
  WHERE [...]
  GROUP BY bpa.invoice_id

但这产生的记录比原始查询少得多，所以这不可能是正确的。我以为是等价的其实不是。我的误解在哪里？

原始查询。如果有帮助，这里是原始查询。

SELECT
    `item`.`id` AS `id`,
                                 `inv`.`member_id` AS `member_id`,
                                 `item`.`sku` AS `sku`,
                                 `item`.`item_name` AS `item_name`,
                                 `item`.`price_ext` AS `item_price`,                                 
                                 ROUND( item.`price_ext` - ( item.`price_ext` * ( ( IFNULL( invbal.`amount_applied`, 0 ) * 100 ) / IFNULL( stm.`amount`, 0 ) ) ) / 100, 2 ) AS `item_price_balance`,
                                 `inv`.`invoice_number` AS `invoice_number`,
                                 `inv`.`invoice_date` AS `invoice_date`,
                                 `inv`.`due_date` AS `due_date`,
                                 CASE
                                    WHEN diritem.`type` = 'dues_membership_levels' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_dues' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_events' THEN 'events'
        WHEN diritem.`type` = 'hidden_finance_discounts' THEN 'finance'
        WHEN diritem.`type` = 'programs_course_products' THEN 'programs'
        WHEN diritem.`type` != '' THEN 'commerce'
        ELSE 'billing'
    END AS `origin`,
                                 IFNULL( diritem.`display_name`, `item`.`sku` ) AS `title`,
                                 diritem.`category` AS `category`,
                                 CONCAT( 'product_' , IFNULL( diritem.`id`, `item`.`sku` ) ) AS `product_id`,
                                 '' AS `start_date`,
                                 '' AS `ticket_name`,
                                 '' AS `ticket_type`
FROM
    `billing_invoice_item` AS `item`
INNER JOIN `billing_invoice` AS `inv` ON
    item.`invoice_id` = inv.`id`
    AND inv.`invoice_type` = ''
LEFT JOIN `billing_statement` AS `stm` ON
    stm.`invoice_number` = inv.`invoice_number`
    AND stm.`trans_id` = inv.`id`
LEFT JOIN (
    SELECT
        SUM( `amount` * -1 ) AS `amount_applied`,
        `invoice_id`
    FROM
        `billing_payments_applied`
    WHERE
        `invoice_id` != 0
        AND ( `apply_date` IS NOT NULL
            AND `apply_date` <= '2023-05-02 23:59:59' )
    GROUP BY
        `invoice_id`) AS invbal ON
    invbal.`invoice_id` = stm.`id`
LEFT JOIN `shopping_sku` sku ON
    sku.`sku` = item.`sku`
    AND sku.`origin` = 'directory'
LEFT JOIN `directory_items` diritem ON
    sku.`product_id` = diritem.`id`
WHERE
    item.`sku` NOT LIKE 'events_%'
    AND IF( inv.`due_date` < IFNULL( stm.`trans_date`, inv.`invoice_date` )
                                ,
    inv.`due_date`
                                ,
    IFNULL( stm.`trans_date`, inv.`invoice_date` ) ) <= '2023-05-02 23:59:59'