多表关联查询去重：获取无重复冗余的SQL结果集

数据表结构及初始化数据 #

CREATE TABLE tbl1 
(
    [ID] [INT]    NULL,
    [Name] [VARCHAR] (50)   NULL
) ;

CREATE TABLE tbl2 
(
    [ID] [INT]    NULL,
    [TranNo] [VARCHAR] (50)   NULL,
    [TranName] [VARCHAR] (50)   NULL  
) ;    

CREATE TABLE tbl3 
(
    [ID] [INT]    NULL,
    [ResultNo] [VARCHAR] (50)   NULL,
    [ResultName] [VARCHAR] (50)   NULL
) ;    

INSERT INTO tbl1 
VALUES (1,'Andy'), (2,'Lisa')

INSERT INTO tbl2 
VALUES (1, 'A1', 'Order'),
       (1, 'A2', 'Order'),
       (1, 'A3', 'Order'),
       (1, 'A4', 'Delivery'),
       (2, 'A5', 'Order'),
       (2, 'A6', 'Delivery'),
       (2, 'A7', 'Delivery')
 
INSERT INTO tbl3
VALUES (1, 'R1', 'Pending'),
       (1, 'R2', 'Success'),
       (2, 'R3', 'Success')

当前查询问题 #

使用以下查询语句：

Select 
    tbl1.*,
    tbl2.TranNo, tbl2.TranName, 
    tbl3.ResultNo, tbl3.ResultName
from 
    tbl1
left outer join
    tbl2 on tbl1.ID = tbl2.ID
left outer join
    tbl3 on tbl1.ID = tbl3.ID

执行后会产生大量笛卡尔积冗余数据，结果如下：

期望结果 #

需要得到无冗余的结果集，以下两种格式均可：

格式一 #

格式二 #

解决方案 #

实现格式一的查询语句 #

WITH ranked_tbl2 AS (
    SELECT 
        ID, TranNo, TranName,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TranNo) AS rn
    FROM tbl2
),
ranked_tbl3 AS (
    SELECT 
        ID, ResultNo, ResultName,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ResultNo) AS rn
    FROM tbl3
)
SELECT 
    t1.ID, t1.Name AS 姓名,
    t2.TranNo, t2.TranName,
    t3.ResultNo, t3.ResultName
FROM tbl1 t1
JOIN ranked_tbl2 t2 ON t1.ID = t2.ID
LEFT JOIN ranked_tbl3 t3 ON t1.ID = t3.ID AND t2.rn = t3.rn
ORDER BY t1.ID, t2.rn;

实现格式二的查询语句 #

WITH ranked_tbl2 AS (
    SELECT 
        ID, TranNo, TranName,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TranNo) AS rn
    FROM tbl2
),
ranked_tbl3 AS (
    SELECT 
        ID, ResultNo, ResultName,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ResultNo) AS rn
    FROM tbl3
),
combined_data AS (
    SELECT 
        t1.ID, t1.Name AS 姓名,
        t2.TranNo, t2.TranName,
        t3.ResultNo, t3.ResultName,
        ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.rn) AS group_rn
    FROM tbl1 t1
    JOIN ranked_tbl2 t2 ON t1.ID = t2.ID
    LEFT JOIN ranked_tbl3 t3 ON t1.ID = t3.ID AND t2.rn = t3.rn
)
SELECT 
    CASE WHEN group_rn = 1 THEN ID ELSE NULL END AS ID,
    CASE WHEN group_rn = 1 THEN 姓名 ELSE NULL END AS 姓名,
    TranNo, TranName,
    ResultNo, ResultName
FROM combined_data
ORDER BY ID, group_rn;

核心思路 #

通过ROW_NUMBER()窗口函数给每个ID下的tbl2、tbl3记录单独编号，仅让相同编号的记录关联，避免了多表直接连接产生的笛卡尔积；格式二额外通过判断是否为当前ID的首行，隐藏重复的ID和姓名字段。

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