多表Inner Join关联tOnlineService时如何避免Cross Join？

尝试将3张表关联后写入第4张表，前两张表（stg.online_purchase和dbo.tmovies）的Inner Join结果符合预期，但加入第3张tOnlineService表后结果集出现类似笛卡尔积的膨胀。

现有SQL代码：

SELECT COUNT(*) 
FROM
    (SELECT
         os.ServiceId AS ServiceId,
         m.MovieId AS MovieId,
         op.user_id AS UserId,
         op.price AS Price,
         op.transaction_id AS TransactionId,
         op.transaction_date AS TransactionDate,
         GETUTCDATE() AS CreatedDate,
         GETUTCDATE() AS ModifiedDate,
         op.source_filename AS SrcFileName
     FROM
         stg.online_purchase op
     INNER JOIN
         dbo.tOnlineService os ON op.online_service_code = os.ServiceCode
                               AND op.online_service_name = os.ServiceName
     INNER JOIN
         dbo.tmovies m ON op.movie_id = m.MovieIdNK) t

数据情况 #

• stg.online_purchase有1000条记录，单独和tMovies关联后能得到1000条预期结果
• tOnlineService由stg.online_purchase生成，同样有1000条记录，但存在重复的ServiceCode+ServiceName组合（比如示例数据里的CCC+Disney Plus出现了两次）
• 只能通过ServiceCode和ServiceName字段关联tOnlineService

示例数据

stg.online_purchase

online_service_name online_service_code movie_id    user_id price   transaction_id  transaction_date    source_filename pipelineId
Disney Plus         CCC                 420245      9698590 6.72    04b3d119-e15d-4879-b1dc-a2dff0bc26a9    2017-11-13 01:10:29 04b3d119e15d4879b1dca2dff0bc26a9.json   e96deeb6-fd5b-49a6-80c9-dc84936eaec1
HBO                 AAA                 411717      8304573 4.69    01f8047e-a972-44f0-b459-046e8c4a9a12    2017-11-06 11:38:51 01f8047ea97244f0b459046e8c4a9a12.json   e96deeb6-fd5b-49a6-80c9-dc84936eaec1
Disney Plus         CCC                 419522      9834543 6.92    041072b6-2952-4bea-a07d-f7217e5a2a51    2017-03-13 20:38:09 041072b629524beaa07df7217e5a2a51.json   e96deeb6-fd5b-49a6-80c9-dc84936eaec1

tOnline_Service

ServiceId   ServiceCode ServiceName CreatedDate ModifiedDate
2001        CCC         Disney Plus 2024-01-07 12:51:28.273 2024-01-07 12:51:28.273
2002        AAA         HBO         2024-01-07 12:51:28.273 2024-01-07 12:51:28.273
2003        CCC         Disney Plus 2024-01-07 12:51:28.273 2024-01-07 12:51:28.273

tMovies

MovieId MovieIdNK   Budget  HomepagePath    Title   OriginalTitle   ReleaseDate Revenue Runtime MovieStatusId   AvgVote CreatedDate ModifiedDate    SrcFileName
49519   100032      0       NULL            The Great Los Angeles Earthquake    The Great Los Angeles Earthquake    1990-11-11  0   180 40  6.8 2023-12-11 15:37:49.723 2023-12-11 15:37:49.723 movies_metadata_20231211152003.csv
49520   100063      0       NULL            Blackout    Blackout    1978-08-25  0   92  40  5.0 2023-12-11 15:37:49.723 2023-12-11 15:37:49.723 movies_metadata_20231211152003.csv
49521   100152      2000000 NULL            Mars    Марс    2004-11-11  0   100 40  5.0 2023-12-11 15:37:49.723 2023-12-11 15:37:49.723 movies_metadata_20231211152003.csv

tOnlineService表中存在重复的ServiceCode+ServiceName组合，比如示例里CCC+Disney Plus对应两个不同的ServiceId（2001和2003）。当用这两个字段关联stg.online_purchase时，每一条匹配的采购记录都会和tOnlineService里所有同组合的记录关联，导致结果集膨胀，看起来像笛卡尔积。

方案1：关联时取tOnlineService的唯一记录 #

如果同一个ServiceCode+ServiceName组合的ServiceId不影响业务（比如任意取一个即可），可以用窗口函数先过滤出每个组合的唯一记录，再关联：

SELECT COUNT(*) 
FROM
    (SELECT
         os.ServiceId AS ServiceId,
         m.MovieId AS MovieId,
         op.user_id AS UserId,
         op.price AS Price,
         op.transaction_id AS TransactionId,
         op.transaction_date AS TransactionDate,
         GETUTCDATE() AS CreatedDate,
         GETUTCDATE() AS ModifiedDate,
         op.source_filename AS SrcFileName
     FROM
         stg.online_purchase op
     INNER JOIN
         (SELECT *,
                 ROW_NUMBER() OVER(PARTITION BY ServiceCode, ServiceName ORDER BY ServiceId) AS rn
          FROM dbo.tOnlineService) os 
         ON op.online_service_code = os.ServiceCode
         AND op.online_service_name = os.ServiceName
         AND os.rn = 1 -- 只取每个组合的第一条记录
     INNER JOIN
         dbo.tmovies m ON op.movie_id = m.MovieIdNK) t

方案2：清理tOnlineService的重复数据 #

如果tOnlineService不应该存在重复的ServiceCode+ServiceName组合，直接清理重复记录：

-- 删除重复记录，保留最小的ServiceId
DELETE FROM dbo.tOnlineService
WHERE ServiceId NOT IN (
    SELECT MIN(ServiceId)
    FROM dbo.tOnlineService
    GROUP BY ServiceCode, ServiceName
)

清理后再执行原关联语句，就能得到1000条预期结果。

方案3：用DISTINCT去重结果集 #

如果业务允许最终结果去重（但不推荐，因为可能会丢失有效数据），可以在子查询里加DISTINCT：

SELECT COUNT(DISTINCT op.transaction_id) -- 或者对所有字段去重
FROM
    (SELECT DISTINCT
         os.ServiceId AS ServiceId,
         m.MovieId AS MovieId,
         op.user_id AS UserId,
         op.price AS Price,
         op.transaction_id AS TransactionId,
         op.transaction_date AS TransactionDate,
         GETUTCDATE() AS CreatedDate,
         GETUTCDATE() AS ModifiedDate,
         op.source_filename AS SrcFileName
     FROM
         stg.online_purchase op
     INNER JOIN
         dbo.tOnlineService os ON op.online_service_code = os.ServiceCode
                               AND op.online_service_name = os.ServiceName
     INNER JOIN
         dbo.tmovies m ON op.movie_id = m.MovieIdNK) t

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