SQL查询无结果且VAT、Total Cost计算失效，寻求解决方法

问题情况 #

执行给定SQL后无结果返回，VAT和Total Cost两个计算字段无法生成有效数值，调整括号、修改*0.05计算逻辑后问题仍存在。原SQL代码如下：

SELECT 
    "ID1", 
    "Date", 
    "KWH Used", 
    "Unit Cost", 
    "Standing Charge", 
    "No Days", 
    "KWH Used" * "Unit Cost" / 100 AS "Electric Cost", 
    "EPG", 
    ( "Electric Cost" ) - ( "EPG" ) AS "Cost after EPG", 
    "No Days" * "Standing Charge" / 100 AS "Total Standing Charge", 
    ( ( "Electric Cost" ) + ( "No Days" ) * ( "Standing Charge" ) / 100 ) * 0.05 AS "VAT", 
    ( "Electric Cost" ) - ( "EPG" ) + ( "No Days" ) * "Standing Charge" / 100 + ( ( "Electric Cost" ) - ( "EPG" ) + ( "No Days" ) * "Standing Charge" / 100 ) * 0.05 AS "Total Cost", 
    ( ( "Electric Cost" ) + ( "No Days" ) * "Standing Charge" / 100 ) * 0.05 AS "Total Cost" 
FROM "EONElectric"

问题原因 #

1. 重复列名冲突：SQL中定义了两个同名的Total Cost字段，部分数据库会直接报错终止查询，导致无结果返回。
2. 别名引用无效：同一层SELECT语句中，不能直接引用刚定义的字段别名（如Electric Cost）进行后续计算，数据库解析顺序会导致别名尚未生效，计算逻辑失效。
3. NULL值干扰：如果KWH Used、Unit Cost等原始字段存在NULL值，直接算术运算会得到NULL结果，表现为“无法生成有效数值”。
4. 表数据为空：需先确认EONElectric表中是否有数据，若表本身无数据，自然返回空结果。

修正后的SQL #

WITH CostCalculations AS (
    SELECT 
        "ID1", 
        "Date", 
        "KWH Used", 
        "Unit Cost", 
        "Standing Charge", 
        "No Days", 
        "EPG",
        -- 用COALESCE处理NULL值，避免计算结果为空
        COALESCE("KWH Used", 0) * COALESCE("Unit Cost", 0) / 100 AS "Electric Cost",
        COALESCE("No Days", 0) * COALESCE("Standing Charge", 0) / 100 AS "Total Standing Charge"
    FROM "EONElectric"
)
SELECT 
    "ID1", 
    "Date", 
    "KWH Used", 
    "Unit Cost", 
    "Standing Charge", 
    "No Days", 
    "Electric Cost",
    "EPG",
    COALESCE("Electric Cost", 0) - COALESCE("EPG", 0) AS "Cost after EPG",
    "Total Standing Charge",
    (COALESCE("Electric Cost", 0) + COALESCE("Total Standing Charge", 0)) * 0.05 AS "VAT",
    -- 简化Total Cost计算：扣除EPG后的成本加固定费用，再乘以1.05（含5%VAT）
    (COALESCE("Electric Cost", 0) - COALESCE("EPG", 0) + COALESCE("Total Standing Charge", 0)) * 1.05 AS "Total Cost"
FROM CostCalculations;

修正说明 #

• 用CTE（公共表达式）提前计算基础成本值，避免重复计算和别名引用错误。
• 加入COALESCE函数处理NULL值，确保即使原始字段为空，也能生成有效数值。
• 删除重复的Total Cost列，简化计算逻辑，避免列名冲突。
• 先执行SELECT COUNT(*) FROM "EONElectric"确认表中是否有数据，若返回0需检查表数据来源。

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