如何连接无外键列的两个SQL表变量实现逐行配对？

Got it, let's fix this 1:1 row pairing problem. The core issue with your earlier attempts is that there's no shared key to link the rows from the two tables—so we need to create one ourselves using row numbers.

Step-by-Step Explanation #

1. Assign Sequential Row Numbers: Use the ROW_NUMBER() window function to generate a unique, ordered index for each row in both table variables. This gives us a way to "match" the first row of one table to the first row of the other, second to second, and so on.
2. Join on the Row Number: Use an INNER JOIN between the two datasets using the generated row number as the join condition. This ensures we get exactly one pair per row, no Cartesian product.
3. Guarantee No Nulls: Since we're using INNER JOIN, only rows with matching row numbers will be included in the result—so you won't get any null values from mismatched counts.

Full Example Code #

DECLARE @InventoryIDList TABLE(ID INT)
DECLARE @ProductSupplierIDList TABLE(ID INT)

-- Insert your sample data
INSERT INTO @InventoryIDList VALUES (123), (456), (789), (111)
INSERT INTO @ProductSupplierIDList VALUES (999), (888), (777), (666)

-- Use CTEs to add row numbers to each table
WITH InventoryRanked AS (
    SELECT 
        ID,
        ROW_NUMBER() OVER (ORDER BY ID) AS RowPosition
    FROM @InventoryIDList
),
SupplierRanked AS (
    SELECT 
        ID,
        ROW_NUMBER() OVER (ORDER BY ID) AS RowPosition
    FROM @ProductSupplierIDList
)

-- Join on the row position to get 1:1 pairs
SELECT
    ir.ID AS InventoryID,
    sr.ID AS ProductSupplierID
FROM InventoryRanked ir
INNER JOIN SupplierRanked sr ON ir.RowPosition = sr.RowPosition

Quick Notes #

• The ORDER BY in ROW_NUMBER() matters! Make sure it sorts the rows in the exact order you want them paired. If you don't have a specific order requirement, you can use ORDER BY (SELECT NULL) in SQL Server to avoid errors (though this doesn't guarantee consistent ordering between runs).
• If the two tables have different row counts, INNER JOIN will only return pairs where both tables have a row at that position. Extra rows from the larger table will be excluded (which aligns with your "no empty values" requirement).

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