MySQL数据透视表按日期排序失败，如何获取最新加密货币价格？

Got it, let's tackle this problem. Your original query had two main issues:

• Using MAX(value) just grabs the highest price value, not the most recent price based on the created timestamp.
• The ORDER BY created DESC clause failed because created isn't in your GROUP BY and isn't functionally dependent on the grouped columns (thanks to sql_mode=only_full_group_by enforcing strict grouping rules).

Here's how to fix this and get the latest prices in the pivot table you want:

Step-by-Step Solution #

First, we need to isolate the most recent price records for each combination of blockchain_currency, currency, and type (buy/sell). Then we can pivot those latest records into your desired format.

Method 1: Using Subqueries & Joins #

This approach works for older MySQL versions that don't support window functions:

SELECT 
    blockchain_currency AS BCCurrency,
    -- Get latest GBP buy/sell prices
    MAX(IF(currency='GBP' AND type='buy', value, 0)) AS GBP_Buy,
    MAX(IF(currency='GBP' AND type='sell', value, 0)) AS GBP_Sell,
    -- Get latest USD buy/sell prices
    MAX(IF(currency='USD' AND type='buy', value, 0)) AS USD_Buy,
    MAX(IF(currency='USD' AND type='sell', value, 0)) AS USD_Sell,
    -- Get latest EUR buy/sell prices
    MAX(IF(currency='EUR' AND type='buy', value, 0)) AS EUR_Buy,
    MAX(IF(currency='EUR' AND type='sell', value, 0)) AS EUR_Sell
FROM (
    -- First, get only the latest records for each (crypto, fiat, type) combo
    SELECT p.blockchain_currency, p.currency, p.type, p.value
    FROM blockchain_currency_price p
    INNER JOIN (
        -- Find the most recent timestamp for each combo
        SELECT 
            blockchain_currency, 
            currency, 
            type, 
            MAX(created) AS latest_created
        FROM blockchain_currency_price
        GROUP BY blockchain_currency, currency, type
    ) latest ON 
        p.blockchain_currency = latest.blockchain_currency
        AND p.currency = latest.currency
        AND p.type = latest.type
        AND p.created = latest.latest_created
) latest_prices
GROUP BY blockchain_currency;

Method 2: Using Window Functions (MySQL 8.0+) #

If you're on MySQL 8.0 or newer, window functions make this cleaner and more readable:

SELECT 
    blockchain_currency AS BCCurrency,
    MAX(IF(currency='GBP' AND type='buy', value, 0)) AS GBP_Buy,
    MAX(IF(currency='GBP' AND type='sell', value, 0)) AS GBP_Sell,
    MAX(IF(currency='USD' AND type='buy', value, 0)) AS USD_Buy,
    MAX(IF(currency='USD' AND type='sell', value, 0)) AS USD_Sell,
    MAX(IF(currency='EUR' AND type='buy', value, 0)) AS EUR_Buy,
    MAX(IF(currency='EUR' AND type='sell', value, 0)) AS EUR_Sell
FROM (
    -- Rank records by creation time (newest first) for each combo
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY blockchain_currency, currency, type 
            ORDER BY created DESC
        ) AS rn
    FROM blockchain_currency_price
) ranked_prices
WHERE rn = 1 -- Only keep the latest record per combo
GROUP BY blockchain_currency;

What This Does #

• Both queries first filter down to only the most recent price entry for every cryptocurrency-fiat-type combination (e.g., ETH-USD-buy, BTC-GBP-sell).
• Then they pivot those latest records into a table where each row is a cryptocurrency, and columns show the latest buy/sell prices for GBP, USD, and EUR.

Expected Result #

Running either query on your table will return the latest prices from the 12:18 updates, not the older 10:55 ones:

+------------+---------+----------+---------+----------+---------+----------+
| BCCurrency | GBP_Buy | GBP_Sell | USD_Buy | USD_Sell | EUR_Buy | EUR_Sell |
+------------+---------+----------+---------+----------+---------+----------+
| BTC        | 7822    | 7667     | 11077   | 10857    | 8912    | 8735     |
| ETH        | 754     | 739      | 1068    | 1046     | 859     | 842      |
+------------+---------+----------+---------+----------+---------+----------+

If you only care about one type (e.g., just buy prices), you can simplify the query by removing the type checks or filtering to type='buy' in the subquery.

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