如何用SQL Server Management Studio 2008R2跨两台电脑的两个表汇总库存数量

Hey there! Let's work through how to sum up the inventory quantities across your two store databases hosted on separate machines. I'll cover a couple of common approaches depending on your database system, and both will give you the exact aggregated result you're looking for.

Key Assumptions #

• We'll focus on SQL-based databases (like SQL Server, MySQL, etc.) since your table structure aligns with that.
• Both databases are accessible over the network (make sure firewalls allow traffic on your database port, e.g., 1433 for SQL Server, 3306 for MySQL).
• Your database user has read access to both remote and local Inventory tables.

Approach 1: Union All + Group By (Works for Most Databases) #

This method is straightforward and works across most SQL systems. We first combine all rows from both tables, then group by ItemNum to sum the quantities.

SELECT
    ItemNum,
    MAX(ItemDescription) AS ItemDescription, -- Uses MAX since ItemNum maps to a unique description
    SUM(Quantity) AS Quantity
FROM (
    -- Local store data (adjust if your local DB path differs)
    SELECT ItemNum, ItemDescription, Quantity 
    FROM StoreInformation.Inventory
    
    UNION ALL
    
    -- Remote store data: adjust the connection path based on your DB system
    SELECT ItemNum, ItemDescription, Quantity 
    FROM [192.168.10.200].StoreInformation.Inventory
) AS combined_inventory
GROUP BY ItemNum
ORDER BY ItemNum;

How It Works:

1. UNION ALL merges all rows from both Inventory tables (including duplicates, which we need to correctly sum quantities).
2. GROUP BY ItemNum groups rows by each unique product ID.
3. SUM(Quantity) adds up the quantities for each product across both stores.
4. MAX(ItemDescription) ensures we get the correct product name (since ItemNum should map to one unique description; MIN works too).

Testing this with your sample data will return exactly the result you want:

ItemNum ItemDescription Quantity
000001 Apple 18
000002 Pear 36

Approach 2: Cross-Server Join (For SQL Server) #

If you're using SQL Server, you can use either linked servers or direct remote queries with OPENROWSET to join the two tables directly.

Option A: Using OPENROWSET (No Linked Server Setup)

This lets you query the remote database directly without pre-configuring a linked server:

SELECT
    COALESCE(local_inv.ItemNum, remote_inv.ItemNum) AS ItemNum,
    COALESCE(local_inv.ItemDescription, remote_inv.ItemDescription) AS ItemDescription,
    ISNULL(local_inv.Quantity, 0) + ISNULL(remote_inv.Quantity, 0) AS Quantity
FROM
    StoreInformation.dbo.Inventory local_inv
FULL OUTER JOIN
    OPENROWSET(
        'SQLNCLI',
        'Server=192.168.10.200;Trusted_Connection=yes;', -- Switch to SQL auth if needed: 'UID=user;PWD=pass;'
        'SELECT ItemNum, ItemDescription, Quantity FROM StoreInformation.dbo.Inventory'
    ) remote_inv
ON local_inv.ItemNum = remote_inv.ItemNum
ORDER BY ItemNum;

Option B: Using a Linked Server

If you set up a linked server (e.g., named Store2Server) pointing to 192.168.10.200, the query becomes cleaner:

SELECT
    COALESCE(local_inv.ItemNum, remote_inv.ItemNum) AS ItemNum,
    COALESCE(local_inv.ItemDescription, remote_inv.ItemDescription) AS ItemDescription,
    ISNULL(local_inv.Quantity, 0) + ISNULL(remote_inv.Quantity, 0) AS Quantity
FROM
    StoreInformation.dbo.Inventory local_inv
FULL OUTER JOIN
    Store2Server.StoreInformation.dbo.Inventory remote_inv
ON local_inv.ItemNum = remote_inv.ItemNum
ORDER BY ItemNum;

How It Works:

• FULL OUTER JOIN ensures we include products that exist in only one store (not just both).
• COALESCE picks the non-null value for ItemNum and ItemDescription (in case a product is missing from one store).
• ISNULL converts null quantities (from missing products) to 0 before summing, so we don't get null results.

Important Notes #

• Network & Permissions: Make sure the two machines can communicate over your database port, and your user account has read access to both databases.
• Data Consistency: Ensure ItemNum is a unique identifier for each product across both stores, and ItemDescription matches for the same ItemNum (otherwise, you might need to clean up data first).

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