mysql - 组合需要不同过滤最大值的 SQL 查询

Question

我有两个 MySQL 查询，它们从几乎相同的查询中查找 MAX() 值。我希望有一种方法可以组合查询以获得更好的性能。

SELECT name, MAX(version) AS highest
FROM publish
WHERE name IN ('alpha', 'beta') AND deprecated = TRUE
GROUP BY name;

SELECT name, MAX(version) AS closed
FROM publish
WHERE name IN ('alpha', 'beta') AND open = FALSE
GROUP BY name;

该架构相当简单。对于这个简单的例子，它看起来像

CREATE TABLE publish
(
    name CHAR(36) NOT NULL,
    version INT NOT NULL,
    deprecated TINYINT NOT NULL,
    open TINYINT DEFAULT 0 NOT NULL,
    PRIMARY KEY (name, version),
);
CREATE INDEX open ON publish (open);
CREATE INDEX deprecated ON publish (deprecated);

Answer 1

您可以使用条件聚合来组合查询

SELECT name, MAX(case when open = FALSE then version end) AS closed,
MAX(case when deprecated = TRUE then version end) AS highest
FROM publish
WHERE name IN ('alpha', 'beta')
GROUP BY name;