内嵌SQL查询报错：Unknown column 'tot' in 'field list'求助

核心问题 #

你遇到的Unknown column 'tot' in 'field list'报错，以及内嵌子查询的逻辑问题，核心原因有两点：

1. 内嵌子查询未给外层列指定别名：虽然子查询内部写了AS tot，但外层子查询的SELECT列表里，这个子查询整体没有被命名为tot，导致最外层SELECT无法识别该列。
2. 子查询逻辑错误：单独执行的子查询会返回多行结果（因为GROUP BY de2.fees_fees），直接放到主查询的SELECT列表里，既会触发语法错误，也无法和当前行的fees_fees关联，得到对应行的总和。

正确解决方案 #

以下两种方式可解决问题，根据数据量和需求选择：

方式一：使用关联子查询 #

修改内嵌子查询，让它针对当前行的fees_fees.fees_id计算对应总和，同时给子查询结果指定别名tot：

SELECT
    fees_id,
    locgov_id,
    locgov_name,
    locgov,
    added_date,
    month,            
    fee,
    type,
    cs,
    ps,
    id,
    tot
FROM (
    SELECT 
        fees_fees.fees_id, 
        fees_locgov.locgov_id AS locgov_id,
        fees_locgov.locgov_name AS locgov_name, 
        fees_fees.locgov,        
        fees_fees.added_date as added_date, 
        fees_fees.month as month, 
        fees_fees.fee as fee,
        fees_fees.type, 
        fees_fees_verify.status AS cs, 
        fees_fees.transfer_status AS ps,
        fees_payback_fees.fees_fees AS id,         
        -- 关联子查询：仅计算当前行fees_id对应的总和
        (SELECT SUM(IFNULL(issued_amount, 0.00)) 
         FROM fees_payback_fees de2 
         WHERE de2.fees_fees = fees_fees.fees_id) AS tot
    from fees_fees
    left join fees_fees_verify on fees_fees_verify.fees_id=fees_fees.fees_id
    left join fees_locgov on fees_locgov.locgov_id=fees_fees.locgov
    left join fees_payback_fees on fees_payback_fees.fees_fees=fees_fees.fees_id
    where fees_fees.status=1 
      AND fees_fees_verify.status='Confirmed' 
) tmp 
group by id 
order by fees_id DESC

方式二：预先计算总和再关联 #

如果数据量较大，关联子查询效率偏低，建议先计算出每个fees_fees对应的总和，再通过JOIN关联到主查询：

SELECT
    fees_id,
    locgov_id,
    locgov_name,
    locgov,
    added_date,
    month,            
    fee,
    type,
    cs,
    ps,
    id,
    COALESCE(t.tot, 0.00) AS tot
FROM (
    SELECT 
        fees_fees.fees_id, 
        fees_locgov.locgov_id AS locgov_id,
        fees_locgov.locgov_name AS locgov_name, 
        fees_fees.locgov,        
        fees_fees.added_date as added_date, 
        fees_fees.month as month, 
        fees_fees.fee as fee,
        fees_fees.type, 
        fees_fees_verify.status AS cs, 
        fees_fees.transfer_status AS ps,
        fees_payback_fees.fees_fees AS id
    from fees_fees
    left join fees_fees_verify on fees_fees_verify.fees_id=fees_fees.fees_id
    left join fees_locgov on fees_locgov.locgov_id=fees_fees.locgov
    left join fees_payback_fees on fees_payback_fees.fees_fees=fees_fees.fees_id
    where fees_fees.status=1 
      AND fees_fees_verify.status='Confirmed' 
) tmp 
-- 左连接预先计算好总和的临时表
LEFT JOIN (
    SELECT fees_fees, SUM(IFNULL(issued_amount, 0.00)) AS tot
    FROM fees_payback_fees
    GROUP BY fees_fees
) t ON t.fees_fees = tmp.id
group by id 
order by fees_id DESC

额外说明 #

• 去掉了不必要的用户变量@fee，直接返回求和结果即可。
• 用COALESCE确保即使没有对应fees_payback_fees记录，tot也会返回0.00而非NULL。

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