复合主键不匹配时如何连接两个SELECT语句？表连接问题求助

Got it, let's work through this join issue you're facing—cartesian products are a common pain point when dealing with composite keys that don't fully align, so let's break this down and find a solution that maps your revenue to owners correctly.

First, let's recap the table structures to make sure we're on the same page:

• Owner Table: 5 composite keys (As_Of_Date, PORT_CD, Deal_Or_Schedule, Deal_Sched_No, owner_port_cd) plus a Percent field
• Position Table: 6 composite keys (As_Of_Date, PORT_CD, Deal_Or_Schedule, Deal_Sched_No, Book_CD_Nme, Positn_Num)

The root of your cartesian product problem is that when you join on only the 4 matching keys, those keys don't uniquely identify a single row in either table. For example, one 4-key combination might map to multiple owner_port_cd entries in Owner, and multiple Book_CD_Nme/Positn_Num entries in Position—so joining them directly creates every possible combination, which is not what you want.

Solution 1: Establish a Hidden Business Mapping Between owner_port_cd and Position Fields #

First, ask yourself: does owner_port_cd have an implicit relationship with any field in the Position table (like Book_CD_Nme)? For example, maybe each owner_port_cd is tied to a specific set of Book_CD_Nme values. If that's the case, you can first create a mapping between these fields, then use it to refine your join:

-- Step 1: Create a mapping between owner_port_cd and Book_CD_Nme (adjust filters based on your business rules)
WITH Owner_Book_Mapping AS (
    SELECT DISTINCT
        o.As_Of_Date,
        o.PORT_CD,
        o.Deal_Or_Schedule,
        o.Deal_Sched_No,
        o.owner_port_cd,
        p.Book_CD_Nme
    FROM Owner o
    JOIN Position p
        ON o.As_Of_Date = p.As_Of_Date
        AND o.PORT_CD = p.PORT_CD
        AND o.Deal_Or_Schedule = p.Deal_Or_Schedule
        AND o.Deal_Sched_No = p.Deal_Sched_No
    -- Add filters here if you know which owner_port_cd maps to which Book_CD_Nme
)

-- Step 2: Use the mapping to join Position and Owner without cartesian products
SELECT
    p.*,
    o.Percent,
    o.owner_port_cd
FROM Position p
JOIN Owner_Book_Mapping obm
    ON p.As_Of_Date = obm.As_Of_Date
    AND p.PORT_CD = obm.PORT_CD
    AND p.Deal_Or_Schedule = obm.Deal_Or_Schedule
    AND p.Deal_Sched_No = obm.Deal_Sched_No
    AND p.Book_CD_Nme = obm.Book_CD_Nme
JOIN Owner o
    ON obm.As_Of_Date = o.As_Of_Date
    AND obm.PORT_CD = o.PORT_CD
    AND obm.Deal_Or_Schedule = o.Deal_Or_Schedule
    AND obm.Deal_Sched_No = o.Deal_Sched_No
    AND obm.owner_port_cd = o.owner_port_cd;

Solution 2: Explicitly Handle One-to-Many/Many-to-Many Relationships (If Business Rules Allow) #

If there's no direct mapping between owner_port_cd and Position fields, you need to clarify how Percent should be applied to Position records. For example:

• Do you need to associate every Owner record with every related Position record (a valid many-to-many relationship)?
• Should you split the Percent across Position records based on a metric (like revenue amount in Position)?

Here's an example of joining while preserving valid many-to-many relationships (and avoiding accidental cartesian products by ensuring you're only getting intended combinations):

SELECT
    p.*,
    o.Percent,
    o.owner_port_cd
    -- If you need to calculate mapped revenue, add something like: p.Revenue * o.Percent AS Owner_Revenue
FROM Position p
JOIN Owner o
    ON p.As_Of_Date = o.As_Of_Date
    AND p.PORT_CD = o.PORT_CD
    AND p.Deal_Or_Schedule = o.Deal_Or_Schedule
    AND p.Deal_Sched_No = o.Deal_Sched_No
-- Add a WHERE clause here if you can filter to only valid Owner-Position pairs
-- For example: WHERE o.owner_port_cd IN (SELECT valid_port FROM Some_Business_Table WHERE Book_CD = p.Book_CD_Nme)
GROUP BY
    p.As_Of_Date,
    p.PORT_CD,
    p.Deal_Or_Schedule,
    p.Deal_Sched_No,
    p.Book_CD_Nme,
    p.Positn_Num,
    o.owner_port_cd,
    o.Percent;

Solution 3: Deduplicate the Owner Table If Keys Are Redundant #

Sometimes, composite keys might be over-defined—maybe the 4 shared keys actually should uniquely identify an Owner record, and owner_port_cd is redundant or has duplicate entries. If that's the case, deduplicate the Owner table first to avoid cartesian products:

-- Deduplicate Owner: keep only unique combinations of the 4 shared keys + owner_port_cd + Percent
WITH Deduplicated_Owner AS (
    SELECT
        As_Of_Date,
        PORT_CD,
        Deal_Or_Schedule,
        Deal_Sched_No,
        owner_port_cd,
        Percent
    FROM Owner
    GROUP BY
        As_Of_Date,
        PORT_CD,
        Deal_Or_Schedule,
        Deal_Sched_No,
        owner_port_cd,
        Percent
    -- If duplicates exist and you need to pick a "primary" record, use ROW_NUMBER():
    -- ROW_NUMBER() OVER (PARTITION BY As_Of_Date, PORT_CD, Deal_Or_Schedule, Deal_Sched_No ORDER BY [Last_Updated_Date] DESC) AS rn
    -- WHERE rn = 1
)

SELECT
    p.*,
    do.Percent,
    do.owner_port_cd
FROM Position p
JOIN Deduplicated_Owner do
    ON p.As_Of_Date = do.As_Of_Date
    AND p.PORT_CD = do.PORT_CD
    AND p.Deal_Or_Schedule = do.Deal_Or_Schedule
    AND p.Deal_Sched_No = do.Deal_Sched_No;

Key Takeaway #

The most critical step here is to clarify your business rules:

• Which Owner records should map to which Position records?
• What does owner_port_cd represent, and how does it relate to the Position table's data?

Once you have that clarity, picking the right technical solution becomes straightforward.

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