MySQL选择没有子查询的最大值

Question

这里有很多关于这个主题的问题(有关示例，请参见 here 或 here)，但我不知道如何正确运行我的特定案例。

这是包含表架构、记录和查询的 SQLFiddle。此刻我有一个有效的查询，但它非常低效，因为它使用了一个依赖子查询:

SELECT 
      id_document, version, id_plant_mov, id_production_type, is_ext, 
      id_cost_center, id_import_kpi_code, id_plant_tag, value 
FROM  document_production_history2 
WHERE id_document = 751 
  AND id_production_type IN (1, 3) 
  AND is_group_production = 0 
  AND (id_document, id_plant_mov, id_production_type, id_cost_center, 
       id_import_kpi_code, id_plant_tag, is_ext, version) 
      IN (
        SELECT id_document, id_plant_mov, id_production_type, 
               id_cost_center, id_import_kpi_code, id_plant_tag, is_ext, 
               MAX(version) 
        FROM document_production_history2 
        GROUP BY id_document, id_plant_mov, id_production_type, 
            id_cost_center, id_import_kpi_code, id_plant_tag, is_ext);

我试着像这样重写上面的查询:

SELECT d.id_document id_doc, d.version, d.id_plant_mov, 
    d.id_production_type id_prod_type, d.is_ext, d.id_cost_center, 
    d.id_import_kpi_code kpi_code, d.id_plant_tag, d.value 
FROM document_production_history2 d 
JOIN (
  SELECT id_document, id_plant_mov, id_production_type, is_ext, 
      id_cost_center, id_import_kpi_code, id_plant_tag, 
      is_group_production, MAX(version) version 
  FROM document_production_history2 
  WHERE id_document = 751 
  AND id_production_type IN (1, 3) 
  AND is_group_production = 0 
  GROUP BY id_document, id_plant_mov, id_production_type, 
      id_cost_center, id_import_kpi_code, id_plant_tag, 
      is_group_production
) m 
ON d.version = m.version 
AND d.id_document = m.id_document 
AND d.id_production_type = m.id_production_type 
AND d.id_plant_mov = m.id_plant_mov 
AND d.id_plant_tag = m.id_plant_tag 
AND d.id_cost_center = m.id_cost_center 
AND d.id_import_kpi_code = m.id_import_kpi_code 
AND d.is_ext = m.is_ext 
AND d.is_group_production = m.is_group_production;

但它返回 27 行而不是预期的 10 行。

提前致谢。

Answer 1

这是一个等同于您的第一个查询的 JOIN。您需要从子查询中取出 is_group_production = 0，并且只在主查询中执行。这会过滤掉最大版本是集体制作的行。

SELECT d.id_document id_doc, d.version, d.id_plant_mov, 
    d.id_production_type id_prod_type, d.is_ext, d.id_cost_center, 
    d.id_import_kpi_code kpi_code, d.id_plant_tag, d.value 
FROM document_production_history2 d 
JOIN (
  SELECT id_document, id_plant_mov, id_production_type, is_ext, 
      id_cost_center, id_import_kpi_code, id_plant_tag, 
      MAX(version) version 
  FROM document_production_history2 
  WHERE id_document = 751 
  AND id_production_type IN (1, 3) 
  GROUP BY id_document, id_plant_mov, id_production_type, 
      id_cost_center, id_import_kpi_code, id_plant_tag

) m 
ON d.version = m.version 
AND d.id_document = m.id_document 
AND d.id_production_type = m.id_production_type 
AND d.id_plant_mov = m.id_plant_mov 
AND d.id_plant_tag = m.id_plant_tag 
AND d.id_cost_center = m.id_cost_center 
AND d.id_import_kpi_code = m.id_import_kpi_code 
AND d.is_ext = m.is_ext 
WHERE d.is_group_production = 0;

DEMO