如何用单条MySQL语句实现股票MarketCap按最新汇率转USD

Got it, let's turn that two-step temporary table approach into a single MySQL query. First, let's recap your original logic: you're fetching the latest records for each stock/trade date, joining with the rate table to map stocks to their currency countries, then using those latest exchange rates to convert local-currency MarketCap values to USD.

Option 1: Using CTEs (MySQL 8.0+) #

This version uses Common Table Expressions for cleaner, more readable code:

WITH latest_aktien_records AS (
    -- Get the latest record for each stock on each trade date (using auto-incremented id)
    SELECT 
        *,
        MAX(id) OVER (PARTITION BY TradeDate, Stock_Short) AS latest_id
    FROM aktien
),
latest_exchange_rates AS (
    -- Map latest stock records to their currency countries and get the FX rate
    SELECT 
        r.country AS LocalCurrencyCountry,
        s.PrevClose AS FXRATE
    FROM latest_aktien_records s
    JOIN rate r ON s.Stock_Short = r.code
    WHERE s.id = s.latest_id
    GROUP BY LocalCurrencyCountry  -- Ensure one rate per currency country
)
-- Calculate USD Market Cap by joining stocks to their corresponding latest rates
SELECT 
    CAST(a.MarketCap AS DECIMAL(20,0)) * ler.FXRATE AS USD_MarketCap,
    ler.FXRATE,
    a.MarketCap AS Local_Currency_MarketCap,
    a.Stock_Short
FROM aktien a
JOIN latest_exchange_rates ler ON a.country = ler.LocalCurrencyCountry
GROUP BY a.country;

Option 2: Using Subqueries (Compatible with Older MySQL Versions) #

If you're using a MySQL version before 8.0 (which doesn't support CTEs), use nested subqueries instead:

SELECT 
    CAST(a.MarketCap AS DECIMAL(20,0)) * rr.FXRATE AS USD_MarketCap,
    rr.FXRATE,
    a.MarketCap AS Local_Currency_MarketCap,
    a.Stock_Short
FROM aktien a
JOIN (
    SELECT 
        r.country AS LocalCurrencyCountry,
        s.PrevClose AS FXRATE
    FROM (
        -- Subquery to get latest record per stock/trade date
        SELECT 
            *,
            MAX(id) OVER (PARTITION BY TradeDate, Stock_Short) AS latest_id
        FROM aktien
    ) s
    JOIN rate r ON s.Stock_Short = r.code
    WHERE s.id = s.latest_id
    GROUP BY LocalCurrencyCountry
) rr ON a.country = rr.LocalCurrencyCountry
GROUP BY a.country;

Key Notes: #

• MarketCap Data Type: Your MarketCap field is stored as a VARCHAR, so we use CAST(a.MarketCap AS DECIMAL(20,0)) to convert it to a numeric type before multiplying by the exchange rate (otherwise, string multiplication will give incorrect results).
• Latest Rate Logic: We use the auto-incremented id field to identify the most recent record for each stock/trade date, since higher id values correspond to newer entries.
• Grouping: The GROUP BY LocalCurrencyCountry ensures we only get one exchange rate per currency country, avoiding duplicate rate values.

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