MySQL分组查询问题：按车型最低售价升序展示车型

Alright, I’ve got you covered here. Let’s walk through how to build this query step by step, starting with clarifying the table structures (since you mentioned three simplified tables, I’ll use common, logical field names to make this concrete):

Assumed Table Structures #

• Models (车型表): id (primary key), model_name (e.g., "Tesla Model 3")
• TrimVersions (衍生版本表): id (primary key), model_id (foreign key to Models.id), trim_name (e.g., "Standard Range")
• PriceRecords (价格记录表): id (primary key), trim_version_id (foreign key to TrimVersions.id), price (numeric price value)

Step-by-Step Query Solution #

We’ll build this in layers to make it easy to follow and modify:

1. Get the lowest price per trim version

First, we need to pull the minimum price for each individual trim version from the price records:

SELECT trim_version_id, MIN(price) AS trim_min_price
FROM PriceRecords
GROUP BY trim_version_id;

2. Get the lowest price per model

Next, we join that result with the TrimVersions table to map each trim to its parent model, then find the minimum price across all trims for each model:

SELECT tv.model_id, MIN(tvp.trim_min_price) AS model_lowest_price
FROM TrimVersions tv
JOIN (
    -- Subquery from step 1
    SELECT trim_version_id, MIN(price) AS trim_min_price
    FROM PriceRecords
    GROUP BY trim_version_id
) tvp ON tv.id = tvp.trim_version_id
GROUP BY tv.model_id;

3. Final query: Join with Models and sort

Finally, we connect this to the Models table to get full model details, then sort by the model’s lowest available price in ascending order:

SELECT 
    m.id AS model_id,
    m.model_name,
    mlp.model_lowest_price
FROM Models m
JOIN (
    -- Subquery from step 2
    SELECT tv.model_id, MIN(tvp.trim_min_price) AS model_lowest_price
    FROM TrimVersions tv
    JOIN (
        SELECT trim_version_id, MIN(price) AS trim_min_price
        FROM PriceRecords
        GROUP BY trim_version_id
    ) tvp ON tv.id = tvp.trim_version_id
    GROUP BY tv.model_id
) mlp ON m.id = mlp.model_id
ORDER BY mlp.model_lowest_price ASC;

Bonus Tips #

• If your PriceRecords includes time-sensitive prices (e.g., different prices for different dates), add a filter in the innermost subquery to target active prices, like:

  SELECT trim_version_id, MIN(price) AS trim_min_price
  FROM PriceRecords
  WHERE effective_date <= CURDATE() -- Only use prices that are currently valid
  GROUP BY trim_version_id;
  

• For better performance, add these indexes:
  • PriceRecords(trim_version_id, price) (covers the innermost grouping and min calculation)
  • TrimVersions(model_id, id) (speeds up joining trims to their parent models)

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