MySQL查询重写没有子查询可能吗？

Question

是否可以在不使用子查询的情况下重写此查询？

SELECT `products`.`id` AS `id`,`products`.`parent_id` AS `parent_id`,`products`.`lft` AS `lft`,`products`.`rght` AS `rght`,`products`.`primary` AS `primary`,`products`.`friendly` AS `friendly`,`products`.`code` AS `code`,`products`.`title` AS `title`,`products`.`meta_description` AS `meta_description`,`products`.`meta_keywords` AS `meta_keywords`,`products`.`name` AS `name`,`products`.`description` AS `description`,`products`.`purchase_price` AS `purchase_price`,`products`.`sale_price` AS `sale_price`,`products`.`tax_code_id` AS `tax_code_id`,`products`.`colour_id` AS `colour_id`,`products`.`size_id` AS `size_id`,`products`.`num_sales` AS `num_sales`,`products`.`showcase` AS `showcase`,`products`.`showcase_image` AS `showcase_image`,`products`.`classic` AS `classic`,`products`.`new_release` AS `new_release`,`products`.`default_colour_id` AS `default_colour_id`,`products`.`deleted` AS `deleted`,`products`.`stock_level` AS `stock_level`,`products`.`hidden` AS `hidden`,`products`.`manufacturer_id` AS `manufacturer_id`,`products`.`created` AS `created`,`products`.`modified` AS `modified`
from `products`
where `products`.`id` NOT IN (
    select `parentproduct`.`id` 
        from `orders`
            join `baskets` on `orders`.`basket_id` = `baskets`.`id`
            join `basket_items` on `basket_items`.`basket_id` = `baskets`.`id` AND `basket_items`.`deleted` = 0
            join `products` on `products`.`id` = `basket_items`.`product_id`
            left join `products` `parentproduct` on `parentproduct`.`id` = `products`.`parent_id`
        group by `parentproduct`.`id`
        having count(*) >= 1
)
AND products.parent_id IS NULL AND products.deleted = 0
ORDER BY name ASC

我确实想出了这个，但是，它仍然考虑到未为同一产品父级订购的产品子级 - 这是否有意义？

SELECT parent_products.id, parent_products.name, orders.id
FROM products
LEFT JOIN products parent_products ON products.parent_id = parent_products.id
LEFT JOIN basket_items ON basket_items.product_id = products.id AND basket_items.deleted = 0
LEFT JOIN baskets ON baskets.id = basket_items.basket_id
LEFT JOIN orders ON orders.basket_id = baskets.id
WHERE orders.id IS NULL
AND products.deleted = 0
AND parent_products.id IS NOT NULL
GROUP BY parent_products.name
ORDER BY parent_products.name ASC

Answer 1

重写此查询的充分理由。我建议将子查询移动到视图中，然后加入该视图。就性能而言，选择会快得多。

或者，

SELECT `products`.... 
from `products`
    left join (
        select `parentproduct`.`id` 
            from `orders`
                join `baskets` on `orders`.`basket_id` = `baskets`.`id`
                join `basket_items` on `basket_items`.`basket_id` = `baskets`.`id` AND `basket_items`.`deleted` = 0
                join `products` on `products`.`id` = `basket_items`.`product_id`
                left join `products` `parentproduct` on `parentproduct`.`id` = `products`.`parent_id`
            group by `parentproduct`.`id`
            having count(*) >= 1
    ) as Counts
    ON products.id = Counts.id
    where Counts.id IS NULL AND products.parent_id IS NULL AND products.deleted = 0
    ORDER BY name ASC

它仍然有一个子查询，但现在子查询仅计算一次，而不是像上面那样按产品 ID 计算。