mysql - 结合多个MySQL UNION查询进行优化

我必须结合这些多个UNION查询...

SELECT 'Mercedes' AS marke, COUNT(autos.id) FROM autos WHERE a1=1
UNION
SELECT 'BMW' AS marke, COUNT(autos.id) FROM autos WHERE a2=1
UNION
SELECT 'Ford' AS marke, COUNT(autos.id) FROM autos WHERE a3=1

如何仅执行一个查询呢？

最佳答案

如果希望摆脱UNION，可以使用带有CASE表达式的聚合查询来解码marke。

SELECT
    CASE
        WHEN a1 = 1 THEN 'Mercedes'
        WHEN a2 = 1 THEN 'BMW'
        WHEN a3 = 1 THEN 'Ford'
    END AS marke,
    COUNT(autos.id)
FROM autos
WHERE a1 = 1 OR a2 = 1 OR a3 = 1
GROUP BY a1, a2, a3