我最终得到了我想要的东西，通过分组，然后应用一个附加的带有“having”的过滤器，这可能不是最好的方法，但我对sql是新的，这是我唯一能实现我想要的东西的方法。。。这是一个“简单”项目列表，其中“数量”为0，范围状态（属性id=168）为中断状态（id 96），“状态”（属性id=97）为启用状态（id=1）。具体如下：

SELECT
  `mgic_catalog_product_entity`.`sku` AS `sku`,
  `mgic_catalog_product_entity`.`type_id` AS `type_id`,
  `mgic_cataloginventory_stock_item`.`qty` AS `qty`,
    MAX(Case WHEN `mgic_eav_attribute`.`attribute_id` = 97 THEN `mgic_catalog_product_entity_int`.`value` END) AS status,
     MAX(Case WHEN `mgic_eav_attribute`.`attribute_id` = 168 THEN `mgic_catalog_product_entity_int`.`value` END) AS range_status
FROM (((`mgic_eav_attribute`
  join `mgic_catalog_product_entity_int` on ((`mgic_eav_attribute`.`attribute_id` = `mgic_catalog_product_entity_int`.`attribute_id`)))
  join `mgic_catalog_product_entity` on ((`mgic_catalog_product_entity_int`.`entity_id` = `mgic_catalog_product_entity`.`entity_id`)))
  join `mgic_cataloginventory_stock_item` on ((`mgic_catalog_product_entity_int`.`entity_id` = `mgic_cataloginventory_stock_item`.`product_id`)))
  WHERE `mgic_catalog_product_entity`.`type_id` = 'simple' AND `mgic_cataloginventory_stock_item`.`qty` = 0
GROUP BY `mgic_catalog_product_entity`.`sku`
HAVING  status = 1 and range_status = 96;

我现在正试图将其修改为更新指令，以便将下面查询的结果列表的“status”（属性\u id=97）设置为disabled（id=2）。

update mgic_eav_attribute ea join 
  mgic_catalog_product_entity_int cpei on ea.attribute_id = cpei.attribute_id join
  mgic_catalog_product_entity cpe  on cpei.entity_id = cpe.entity_id join
  mgic_cataloginventory_stock_item cisi  on cpei.entity_id = cisi.product_id
  set cpei.value = 2
  WHERE cpe.type_id = 'simple' AND ((ea.attribute_code = 'status') and
  (cpei.value = 1)) AND cisi.qty = 0 AND EXISTS(
   SELECT
  cpe.sku,
  cpe.type_id,
  cisi.qty,
    MAX(Case WHEN ea.attribute_id = 97 THEN cpei.value END) AS status,
     MAX(Case WHEN ea.attribute_id = 168 THEN cpei.value END) AS range_status
FROM (((`mgic_eav_attribute`
  join `mgic_catalog_product_entity_int` on ((`mgic_eav_attribute`.`attribute_id` = `mgic_catalog_product_entity_int`.`attribute_id`)))
  join `mgic_catalog_product_entity` on ((`mgic_catalog_product_entity_int`.`entity_id` = `mgic_catalog_product_entity`.`entity_id`)))
  join `mgic_cataloginventory_stock_item` on ((`mgic_catalog_product_entity_int`.`entity_id` = `mgic_cataloginventory_stock_item`.`product_id`)))
  WHERE `mgic_catalog_product_entity`.`type_id` = 'simple' AND `mgic_cataloginventory_stock_item`.`qty` = 0
GROUP BY cpei.entity_id
HAVING  status = 1 and range_status = 96);

上面的查询似乎挂起了，没有执行。有人能帮忙吗？