MySQL多SELECT语句运算符使用求助：林地占比计算报错

错误原因 #

你写的子查询返回了Year、State、Hectares三列，而+运算符只能对单个数值列进行运算，这就是触发“operand should contain 1 column(s)”错误的核心原因。另外，DISTINCT在这里完全没必要，你需要的是按年份和州分组，分别统计林地、非林地的面积总和。

正确写法 #

方法1：条件聚合（推荐） #

直接在一个查询里用SUM(CASE...)分别计算两类面积，再推导占比：

SELECT 
    Year,
    State,
    SUM(CASE WHEN Category = 'Forested' THEN Hectares ELSE 0 END) AS Forested_Area,
    SUM(CASE WHEN Category = 'Non-Forested' THEN Hectares ELSE 0 END) AS Non_Forested_Area,
    -- 计算占比，用NULLIF避免分母为0报错
    ROUND(
        SUM(CASE WHEN Category = 'Forested' THEN Hectares ELSE 0 END) / 
        NULLIF(SUM(Hectares), 0),
        4 -- 保留4位小数，可按需调整
    ) AS Forested_Ratio
FROM forest_greenproject.`forested and non-forested areas, malaysia, 2000 - 2017`
GROUP BY Year, State
ORDER BY Year, State;

方法2：子查询分组求和后关联 #

如果习惯用子查询拆分逻辑，可以先分别统计两类面积，再关联计算：

WITH Forested AS (
    SELECT Year, State, SUM(Hectares) AS Forested_Area
    FROM forest_greenproject.`forested and non-forested areas, malaysia, 2000 - 2017`
    WHERE Category = 'Forested'
    GROUP BY Year, State
),
NonForested AS (
    SELECT Year, State, SUM(Hectares) AS Non_Forested_Area
    FROM forest_greenproject.`forested and non-forested areas, malaysia, 2000 - 2017`
    WHERE Category = 'Non-Forested'
    GROUP BY Year, State
)
SELECT 
    f.Year,
    f.State,
    f.Forested_Area,
    n.Non_Forested_Area,
    ROUND(f.Forested_Area / NULLIF(f.Forested_Area + n.Non_Forested_Area, 0), 4) AS Forested_Ratio
FROM Forested f
JOIN NonForested n ON f.Year = n.Year AND f.State = n.State
ORDER BY f.Year, f.State;

关键说明 #

• 用GROUP BY Year, State确保按年份和州分组统计，替代无用的DISTINCT
• NULLIF函数用来规避分母为0的报错（若某组无任何面积数据，返回NULL而非报错）
• ROUND函数用来控制占比的小数位数，可根据需求调整精度

内容的提问来源于stack exchange，提问作者Syarqawi Ruslan