Oracle SQL三表关联：车型与邮编销量统计及排序问题求助

Absolutely—you can pull off this combined statistic in a single SELECT using standard JOIN syntax. Let’s fix your original query and break down the solution step by step.

What Was Wrong With Your Original Query #

• You used PARTITION BY s.vehicleVIN, which counts sales per individual vehicle (unique VIN), not per model. That’s why identical vehicle models weren’t rolling up into a single total sales figure.
• The DISTINCT clause only removes exact duplicate rows, but since each sale can tie to a unique customer zip, it couldn’t collapse same-model entries into one row.

Solution Query #

This approach precomputes the aggregate totals for models and zips first, then joins them back to the core sales data to get unique model-zip pairs with their respective totals:

SELECT DISTINCT
    v.vehicleModel,
    model_sales.Sales_By_Model,
    c.customerZip,
    zip_sales.Sales_By_ZIP
FROM SALES s
INNER JOIN VEHICLES v 
    ON s.vehicleVIN = v.vehicleVIN
INNER JOIN CUSTOMERS c 
    ON s.customerID = c.customerID
INNER JOIN (
    -- Calculate total sales per vehicle model
    SELECT 
        v_inner.vehicleModel,
        COUNT(*) AS Sales_By_Model
    FROM SALES s_inner
    INNER JOIN VEHICLES v_inner 
        ON s_inner.vehicleVIN = v_inner.vehicleVIN
    GROUP BY v_inner.vehicleModel
) model_sales 
    ON v.vehicleModel = model_sales.vehicleModel
INNER JOIN (
    -- Calculate total sales per customer zip code
    SELECT 
        c_inner.customerZip,
        COUNT(*) AS Sales_By_ZIP
    FROM SALES s_inner
    INNER JOIN CUSTOMERS c_inner 
        ON s_inner.customerID = c_inner.customerID
    GROUP BY c_inner.customerZip
) zip_sales 
    ON c.customerZip = zip_sales.customerZip
ORDER BY model_sales.Sales_By_Model DESC, zip_sales.Sales_By_ZIP DESC;

How This Works #

1. Subqueries for Aggregates:
   • model_sales: Groups sales by vehicle model to get the total number of sales per model.
   • zip_sales: Groups sales by customer zip code to get total sales per zip.
2. Joins: We link these precomputed totals back to the original sales, vehicle, and customer data to map each unique model-zip pair to their respective totals.
3. DISTINCT: Ensures we only show each model-zip combination once (since multiple sales could exist for the same model and zip).
4. Sorting: Orders results first by model sales (highest to lowest), then by zip sales (highest to lowest), matching your requirement.

If You Want Separate Model and Zip Lists #

If your goal was to list model totals and zip totals as separate sections in the same result set (instead of paired model-zip rows), use UNION ALL:

SELECT 
    vehicleModel,
    Sales_By_Model,
    NULL AS customerZip,
    NULL AS Sales_By_ZIP
FROM (
    SELECT 
        v.vehicleModel,
        COUNT(*) AS Sales_By_Model
    FROM SALES s
    INNER JOIN VEHICLES v ON s.vehicleVIN = v.vehicleVIN
    GROUP BY v.vehicleModel
) model_sales
UNION ALL
SELECT 
    NULL AS vehicleModel,
    NULL AS Sales_By_Model,
    customerZip,
    Sales_By_ZIP
FROM (
    SELECT 
        c.customerZip,
        COUNT(*) AS Sales_By_ZIP
    FROM SALES s
    INNER JOIN CUSTOMERS c ON s.customerID = c.customerID
    GROUP BY c.customerZip
) zip_sales
ORDER BY COALESCE(Sales_By_Model, Sales_By_ZIP) DESC;

Test With Your Sample Data #

Using the sample rows you provided, the first query will return:

Which matches the expected structure—each model and zip shows their respective total sales.

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