如何正确对子查询生成的结果表执行SELECT并解决重复列名报错

报错原因 #

你触发的Error Code: 1060. Duplicate column name 'currency'和外层的WHERE筛选逻辑无关，问题出在子查询的SELECT *写法：

• 关联的transactions表和currency_details表都有currency字段，用*拉取全表字段时，查询结果会出现两个同名的currency列，外层查询解析*时就会触发重复列名校验报错。

修复方案 #

方案1（推荐）：明确指定需要的字段，避免用*拉取全量 #

按需罗列查询字段，重复的currency字段仅保留一次即可，写法如下：

SELECT * FROM
(
SELECT 
    t.id, -- 替换为你实际需要的transactions表字段
    t.amount,
    t.currency,
    fx.rate,
    cd.exponent,
    t.amount / POWER(10,  cd.exponent) * fx.rate AS usd_equivalent 
FROM new_schema.transactions t
    JOIN new_schema.fx_rates fx
    ON t.currency = fx.ccy
    JOIN new_schema.currency_details cd
    ON t.currency = cd.currency
    WHERE fx.base_ccy = 'USD'
) result
WHERE result.usd_equivalent > 10;

方案2：给重复字段单独起别名 #

如果你确实需要拉取两个表的所有字段，只需给重名的currency字段加别名区分即可：

SELECT * FROM
(
SELECT 
    t.*,
    fx.*,
    cd.currency AS cd_currency, -- 给currency_details表的currency字段单独命名，避免重复
    cd.exponent,
    -- 其他currency_details表字段按需添加即可
    t.amount / POWER(10,  cd.exponent) * fx.rate AS usd_equivalent 
FROM new_schema.transactions t
    JOIN new_schema.fx_rates fx
    ON t.currency = fx.ccy
    JOIN new_schema.currency_details cd
    ON t.currency = cd.currency
    WHERE fx.base_ccy = 'USD'
) result
WHERE result.usd_equivalent > 10;

方案3：简化嵌套写法，直接在主查询过滤 #

不需要嵌套子查询也可以实现需求，性能更优：

SELECT 
    -- 按需指定查询字段，避免重复列名
    t.amount,
    t.currency,
    fx.rate,
    cd.exponent,
    t.amount / POWER(10,  cd.exponent) * fx.rate AS usd_equivalent 
FROM new_schema.transactions t
    JOIN new_schema.fx_rates fx
    ON t.currency = fx.ccy
    JOIN new_schema.currency_details cd
    ON t.currency = cd.currency
WHERE 
    fx.base_ccy = 'USD'
    AND (t.amount / POWER(10,  cd.exponent) * fx.rate) > 10;

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