基于Northwind数据库的国家间贸易余额统计技术需求

Alright, let's tackle this trade balance task for the old Northwind database. The goal is to calculate each country's total exports, imports, and resulting trade balance (exports minus imports), all using friendly country names instead of IDs.

First, let's clarify the definitions to make sure we're on the same page:

• Exports: Total value of goods sold by suppliers from Country A to customers in other countries.
• Imports: Total value of goods purchased by customers in Country A from suppliers in other countries.
• Trade Balance: Exports - Imports (positive = trade surplus, negative = trade deficit).

We'll need to tie together several tables here: Countries (to map IDs to names), Suppliers, Customers, Orders, and Order Details (to calculate transaction values).

Complete SQL Query #

WITH CountryExports AS (
    -- Calculate exports for each country (supplier's country selling to foreign customers)
    SELECT
        c.CountryName AS ExportingCountry,
        SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalExports
    FROM Suppliers s
    JOIN Countries c ON s.CountryID = c.CountryID
    JOIN Orders o ON s.SupplierID = o.SupplierID
    JOIN Customers cust ON o.CustomerID = cust.CustomerID
    JOIN [Order Details] od ON o.OrderID = od.OrderID
    WHERE s.CountryID != cust.CountryID -- Only cross-border sales
    GROUP BY c.CountryName
),
CountryImports AS (
    -- Calculate imports for each country (customer's country buying from foreign suppliers)
    SELECT
        c.CountryName AS ImportingCountry,
        SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalImports
    FROM Customers cust
    JOIN Countries c ON cust.CountryID = c.CountryID
    JOIN Orders o ON cust.CustomerID = o.CustomerID
    JOIN Suppliers s ON o.SupplierID = s.SupplierID
    JOIN [Order Details] od ON o.OrderID = od.OrderID
    WHERE cust.CountryID != s.CountryID -- Only cross-border purchases
    GROUP BY c.CountryName
)
-- Combine exports and imports to get trade balance
SELECT
    COALESCE(ce.ExportingCountry, ci.ImportingCountry) AS CountryName,
    ROUND(COALESCE(ce.TotalExports, 0), 2) AS TotalExports,
    ROUND(COALESCE(ci.TotalImports, 0), 2) AS TotalImports,
    ROUND(COALESCE(ce.TotalExports, 0) - COALESCE(ci.TotalImports, 0), 2) AS TradeBalance
FROM CountryExports ce
FULL OUTER JOIN CountryImports ci ON ce.ExportingCountry = ci.ImportingCountry
ORDER BY TradeBalance DESC;

Key Details Explained #

• Transaction Value Calculation: We use od.UnitPrice * od.Quantity * (1 - od.Discount) to get the actual amount paid after discounts, which is the accurate transaction value for trade calculations.
• Cross-Border Filter: The WHERE clauses exclude domestic transactions (where supplier and customer are in the same country) since we only care about international trade.
• Handling Missing Values: COALESCE ensures countries with only exports or only imports still show up in the results, with 0 for the missing metric.
• FULL OUTER JOIN: This guarantees every country present in either exports or imports is included in the final output.
• Rounding: ROUND cleans up the decimal values to make the results more readable.

If you need to adjust for specific edge cases (like including domestic trade, or filtering by continent), you can tweak the WHERE clause or add a join to the ContinentID field in the Countries table.

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