关于此主题这里有很多问题(请参阅此处或此处的示例),但是我无法弄清楚如何正确运行我的特定案例。
这是包含表架构,记录和查询的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行。
提前致谢。
这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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句