联接多个表以显示整个数据，而无需交叉联接-解网

问：

首先，我很抱歉我的问题标题没有有点模糊。

我将尝试在这里详细解释。

我正在尝试加入超过 4 张桌子。他们每个人都有structure_id作为其中的列。

这些表格及其名称如下所示;

结构表：

结构 ID	名字	不。跨度数
5	弗雷泽	4
6	奇洛钦	6
7	金洛格	3
8	白肋	5
9	循环	6
10	羽扇豆	5

跨度轴承台：

结构 ID	B盘编号
5	1
5	1
5	2
5	2
5	3
5	3
5	4
5	4
6	1
6	1
6	1
6	2
6	2
6	3
6	3
6	4
6	4
6	4
6	5
6	5
6	6
6	6
7	（空）
8	1
8	1
8	2
8	2
8	3
8	3
8	4
8	4
8	5
8	5
9	1
10	1
10	1
10	2
10	2
10	3
10	3
10	4
10	4
10	5
10	5

跨度表：

结构 ID	跨度编号
5	1
5	2
5	3
5	4
6	1
6	2
6	3
6	4
6	5
6	6
7	1
7	2
7	3
8	1
8	2
8	3
8	4
8	5
9	1
9	2
9	3
9	4
9	5
9	6
10	1
10	2
10	3
10	4
10	5

子结构表：

结构 ID	元素编号
5	1
5	2
5	3
5	4
5	5
6	1
6	2
6	3
6	4
7	1
7	2
7	3
7	4
8	85
8	86
8	87
8	88
8	89
8	90
9	（空）
10	1
10	2
10	3
10	4
10	5
10	6

用于创建和插入数据的 SQL 查询如下;

CREATE TABLE structures (structure_id INT, name VARCHAR(20), no_of_spans INT);
CREATE TABLE span_bearing (structure_id INT, B_Span_no INT);
CREATE TABLE span (structure_id INT, Span_no INT);
CREATE TABLE substructure (structure_id INT, Structure_element_no INT);

INSERT INTO structures (structure_id,name,no_of_spans) VALUES(5,"Fraser",4);
INSERT INTO structures (structure_id,name,no_of_spans) VALUES(6,"Chiloctin",6);
INSERT INTO structures (structure_id,name,no_of_spans) VALUES(7,"Kimlog",3);
INSERT INTO structures (structure_id,name,no_of_spans) VALUES(8,"Burley",5);
INSERT INTO structures (structure_id,name,no_of_spans) VALUES(9,"Loops",6);
INSERT INTO structures (structure_id,name,no_of_spans) VALUES(10,"Lupin",5);

INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(5,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,5);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,5);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,6);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(6,6);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(7,NULL);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,5);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(8,5);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(9,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,1);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,2);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,3);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,4);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,5);
INSERT INTO span_bearing (structure_id,B_Span_no) VALUES(10,5);

INSERT INTO span (structure_id,Span_no) VALUES(5,1);
INSERT INTO span (structure_id,Span_no) VALUES(5,2);
INSERT INTO span (structure_id,Span_no) VALUES(5,3);
INSERT INTO span (structure_id,Span_no) VALUES(5,4);
INSERT INTO span (structure_id,Span_no) VALUES(6,1);
INSERT INTO span (structure_id,Span_no) VALUES(6,2);
INSERT INTO span (structure_id,Span_no) VALUES(6,3);
INSERT INTO span (structure_id,Span_no) VALUES(6,4);
INSERT INTO span (structure_id,Span_no) VALUES(6,5);
INSERT INTO span (structure_id,Span_no) VALUES(6,6);
INSERT INTO span (structure_id,Span_no) VALUES(7,1);
INSERT INTO span (structure_id,Span_no) VALUES(7,2);
INSERT INTO span (structure_id,Span_no) VALUES(7,3);
INSERT INTO span (structure_id,Span_no) VALUES(8,1);
INSERT INTO span (structure_id,Span_no) VALUES(8,2);
INSERT INTO span (structure_id,Span_no) VALUES(8,3);
INSERT INTO span (structure_id,Span_no) VALUES(8,4);
INSERT INTO span (structure_id,Span_no) VALUES(8,5);
INSERT INTO span (structure_id,Span_no) VALUES(9,1);
INSERT INTO span (structure_id,Span_no) VALUES(9,2);
INSERT INTO span (structure_id,Span_no) VALUES(9,3);
INSERT INTO span (structure_id,Span_no) VALUES(9,4);
INSERT INTO span (structure_id,Span_no) VALUES(9,5);
INSERT INTO span (structure_id,Span_no) VALUES(9,6);
INSERT INTO span (structure_id,Span_no) VALUES(10,1);
INSERT INTO span (structure_id,Span_no) VALUES(10,2);
INSERT INTO span (structure_id,Span_no) VALUES(10,3);
INSERT INTO span (structure_id,Span_no) VALUES(10,4);
INSERT INTO span (structure_id,Span_no) VALUES(10,5);

INSERT INTO substructure (structure_id,Structure_element_no) VALUES(5,1);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(5,2);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(5,3);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(5,4);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(5,5);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(6,1);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(6,2);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(6,3);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(6,4);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(7,1);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(7,2);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(7,3);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(7,4);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(8,85);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(8,86);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(8,87);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(8,88);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(8,89);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(8,90);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(9,NULL);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(10,1);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(10,2);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(10,3);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(10,4);
INSERT INTO substructure (structure_id,Structure_element_no) VALUES(10,5);

我想要的结果是：

结构 ID	名字	不。跨度数	BSpan_no	Span_no	元素编号
5	弗雷泽	4	1	1	1
5	弗雷泽	4	1	2	2
5	弗雷泽	4	2	3	3
5	弗雷泽	4	2	4	4
5	弗雷泽	4	3		5
5	弗雷泽	4	3
5	弗雷泽	4	4
5	弗雷泽	4	4
6	奇洛钦	6	1	1	1
6	奇洛钦	6	1	2	2
6	奇洛钦	6	1	3	3
6	奇洛钦	6	2	4	4
6	奇洛钦	6	2	5
6	奇洛钦	6	3	6
6	奇洛钦	6	3
6	奇洛钦	6	4
6	奇洛钦	6	4
6	奇洛钦	6	4
6	奇洛钦	6	5
6	奇洛钦	6	5
6	奇洛钦	6	6
6	奇洛钦	6	6
7	金洛格	3		1	1
7	金洛格	3		2	2
7	金洛格	3		3	3
7	金洛格	3			4
8	白肋	5	1	1	85
8	白肋	5	1	2	86
8	白肋	5	2	3	87
8	白肋	5	2	4	88
8	白肋	5	3	5	89
8	白肋	5	3		90
8	白肋	5	4
8	白肋	5	4
8	白肋	5	5
8	白肋	5	5
9	循环	6		1	1
9	循环	6		2
9	循环	6		3
9	循环	6		4
9	循环	6		5
9	循环	6		6
10	羽扇豆	5	1	1	1
10	羽扇豆	5	1	2	2
10	羽扇豆	5	2	3	3
10	羽扇豆	5	2	4	4
10	羽扇豆	5	3	5	5
10	羽扇豆	5	3		6
10	羽扇豆	5	4
10	羽扇豆	5	4
10	羽扇豆	5	5
10	羽扇豆	5	5

从表中可以看出，某些结构存在某种数据不匹配，即特定结构的信息在一个表中可用，但其相应的数据在其他表中部分可用或完全不可用。

这确实引起了很多问题，因为我正在处理 8000 多个结构。

在更大程度上，我通过使用 UNION ALL 实现了预期的输出。但是我的代码变得太长（即超过 3000 行）并且太重复。但是，仍然有一些结构没有显示完整的信息。

以下是我使用的查询示例：

SELECT 
   
    STR_ID,
    NAME,
    No_of_Spans
    SUBSTRUCTURE_ELEMENT_NO,
    BSpan_no,
    Span_no,
    
    ID_1
    
FROM 

(

WITH span AS (
        
        SELECT STRUCTURE_ID,span_no,ROW_NUMBER() OVER (PARTITION BY STRUCTURE_ID ORDER BY STRUCTURE_ID) Span_ID
        FROM span

            )
SELECT 
     
    STR_ID,
    NAME,
    No_of_Spans
    SUBSTRUCTURE_ELEMENT_NO,
    BSpan_no, 
    span.span_no,
    Span_Brng
    ID_1

FROM (

WITH subs AS (
        
        SELECT STRUCTURE_ID,SUBSTRUCTURE_ELEMENT_NO,ROW_NUMBER() OVER (PARTITION BY STRUCTURE_ID ORDER BY SUBSTRUCTURE_ELEMENT_NO) Subs_ID
           FROM substructure         
)

SELECT 
    
    STR_ID,
    NAME,
    No_of_Spans
    subs.SUBSTRUCTURE_ELEMENT_NO,
    BSpan_no,
    span_no_1,
    
    Span_Brng,
    ID_1
    
FROM (

SELECT 
    
    
    STR_ID,
    NAME,
    No_of_Spans
    BSpan_no, 
    Span_Brng
    ROW_NUMBER() OVER (PARTITION BY STR_ID ORDER BY STR_ID) ID_1 


FROM (


WITH bspan AS ( 
                SELECT STRUCTURE_ID,BSPAN_NO,ROW_NUMBER() OVER (PARTITION BY structure_ID ORDER BY structure_ID) Span_Brng
                FROM span_bearing
            )
SELECT 
    

    STR_ID,
    NAME,
    No_of_Spans
       
    bspan.bspan_no,
    bspan.Span_Brng


    

FROM 
                            
    STRUCTURE a    
    LEFT JOIN rs1 ON a.structure_id=rs1.structure_id, 
      
)

)tbl1 

LEFT JOIN rs2 ON  tbl1.STR_ID = rs2.STRUCTURE_ID AND tbl1.ID_1 = rs2.Subs_ID


) tbl2

LEFT JOIN rs3 ON tbl2.Str_ID = rs3.structure_id AND tbl2.ID_1=rs3.span_id

WHERE tbl2.bspan_no IS NOT NULL

上面的 SQL 查询只是我整个代码的一个示例。它被大大简化了。

在我的代码中，必须使用最后一行，即 WHERE tbl2.bspan_no IS NOT NULL。如果不使用它，那么我得到的结果是：

如此多的结构都存在数据问题。我不能继续使用 UNION ALL 来适应这些结构。

请告诉我，问题是因为左联接而出现吗？
UNION ALL有其他替代品吗？
有什么方法可以连接多个表，其中任何表中的每个数据都完全显示而无需交叉连接？

一如既往，您的支持将不胜感激。

如果我不能正确解释我的问题，我再次道歉。如果您想了解更多信息，请告诉我。

sql-server 联接

答：

2赞 seanb 11/13/2023 #1

我发现这样的诀窍是为每个值制作一个假的“行号”，表示它将在哪一行上报告（对于给定的结构）。

最简单的方法

对于结构表，只需将其与“数字”表交叉连接（根据需要制作尽可能多的行）
对于其他表（spans 等），请使用 ROW_NUMBER 函数对它们进行排序并提供相关行

因此，对于每个表，它基本上都有一个由两部分组成的“键”

Structure_ID
该结构的行号

下面的代码在 CTE 中执行此操作，然后只需 LEFT 将其他表连接到结构表即可

代码/示例可在 db<>fiddle 上找到

笔记

编辑：这以前使用临时表，但现在是原始名称。db<>fiddle 当前使用临时表。
“Nums”表（有 1、2、3 等）目前限制为 40 个。这应该与任何单个结构所需的最大行数一样大或更大。
- 编辑：由于 OP 的交叉连接 VALUES 问题，Nums 表逻辑已替换为 SELECT ROW_NUMBER
以下输出包括行号（Nums.n）以供说明。随意删除使用中的它。

WITH    SP_B AS (SELECT structure_id,B_Span_no, 
                        ROW_NUMBER() OVER (PARTITION BY structure_id ORDER BY B_Span_no) AS rn 
                FROM span_bearing),
        SP AS   (SELECT structure_id,Span_no, 
                        ROW_NUMBER() OVER (PARTITION BY structure_id ORDER BY Span_no) AS rn 
                FROM span),
        SubS AS (SELECT structure_id,Structure_element_no, 
                        ROW_NUMBER() OVER (PARTITION BY structure_id ORDER BY Structure_element_no) AS rn 
                FROM substructure),
        Nums AS (SELECT TOP 40 ROW_NUMBER() OVER (ORDER BY i) AS n FROM
                    (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS A
                    CROSS JOIN (SELECT 1 AS j UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS B
                    CROSS JOIN (SELECT 1 AS k UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS C
                )
        -- Nums AS (SELECT 1 + (Digits.n) + (Tens.n * 10) AS n 
        --        FROM    (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS Digits(n) 
        --                CROSS JOIN (VALUES (0), (1), (2), (3)) AS Tens(n))
    SELECT      structures.structure_id,
                Nums.n,
                structures.name,
                Structures.no_of_spans,
                SP_B.B_Span_no,
                SP.Span_no,
                SubS.Structure_element_no
        FROM    structures
                CROSS JOIN Nums
                LEFT OUTER JOIN SP_B ON structures.structure_id = SP_B.structure_id AND Nums.n = SP_B.rn
                LEFT OUTER JOIN SP   ON structures.structure_id = SP.structure_id   AND Nums.n = SP.rn
                LEFT OUTER JOIN SubS ON structures.structure_id = SubS.structure_id AND Nums.n = SubS.rn
        WHERE   SP_B.B_Span_no IS NOT NULL
                OR SP.Span_no IS NOT NULL
                OR SubS.Structure_element_no IS NOT NULL
        ORDER BY structures.structure_id, Nums.n;

结果 - 注意到 7 的预期结果（Kimlog）

structure_id n           name                 no_of_spans B_Span_no   Span_no     Structure_element_no
------------ ----------- -------------------- ----------- ----------- ----------- --------------------
5            1           Fraser               4           1           1           1
5            2           Fraser               4           1           2           2
5            3           Fraser               4           2           3           3
5            4           Fraser               4           2           4           4
5            5           Fraser               4           3           NULL        5
5            6           Fraser               4           3           NULL        NULL
5            7           Fraser               4           4           NULL        NULL
5            8           Fraser               4           4           NULL        NULL
6            1           Chiloctin            6           1           1           1
6            2           Chiloctin            6           1           2           2
6            3           Chiloctin            6           1           3           3
6            4           Chiloctin            6           2           4           4
6            5           Chiloctin            6           2           5           NULL
6            6           Chiloctin            6           3           6           NULL
6            7           Chiloctin            6           3           NULL        NULL
6            8           Chiloctin            6           4           NULL        NULL
6            9           Chiloctin            6           4           NULL        NULL
6            10          Chiloctin            6           4           NULL        NULL
6            11          Chiloctin            6           5           NULL        NULL
6            12          Chiloctin            6           5           NULL        NULL
6            13          Chiloctin            6           6           NULL        NULL
6            14          Chiloctin            6           6           NULL        NULL
7            1           Kimlog               3           NULL        1           1
7            2           Kimlog               3           NULL        2           2
7            3           Kimlog               3           NULL        3           3
7            4           Kimlog               3           NULL        NULL        4
8            1           Burley               5           1           1           85
8            2           Burley               5           1           2           86
8            3           Burley               5           2           3           87
8            4           Burley               5           2           4           88
8            5           Burley               5           3           5           89
8            6           Burley               5           3           NULL        90
8            7           Burley               5           4           NULL        NULL
8            8           Burley               5           4           NULL        NULL
8            9           Burley               5           5           NULL        NULL
8            10          Burley               5           5           NULL        NULL
9            1           Loops                6           1           1           NULL
9            2           Loops                6           NULL        2           NULL
9            3           Loops                6           NULL        3           NULL
9            4           Loops                6           NULL        4           NULL
9            5           Loops                6           NULL        5           NULL
9            6           Loops                6           NULL        6           NULL
10           1           Lupin                5           1           1           1
10           2           Lupin                5           1           2           2
10           3           Lupin                5           2           3           3
10           4           Lupin                5           2           4           4
10           5           Lupin                5           3           5           5
10           6           Lupin                5           3           NULL        NULL
10           7           Lupin                5           4           NULL        NULL
10           8           Lupin                5           4           NULL        NULL
10           9           Lupin                5           5           NULL        NULL
10           10          Lupin                5           5           NULL        NULL

0赞 ar ia 11/13/2023

非常感谢亲爱的肖恩。这无疑给了我解决此类问题的新方法。我现在遇到的唯一问题是 SQL-Server（我认为！！在行“ FROM （VALUES （0），（1）， ...”它给出错误“无效的表名”。最重要的是，我无法创建任何新表，也无法修改数据库中的现有表。

0赞 seanb 11/13/2023

嗨，@ar - 我以两种方式修改了我的回答。1）删除了临时表并使用了它们的原始名称（这里没有创建新表等 - 我只是使用临时表进行测试）。2）更改了 Nums 表的生成方式。请注意，这些更改并未改变结果。但是，您具有带有双引号的值，例如，并且用于生成数字的 VALUES 方法存在问题，这表明这不是 SQL Server，而是其他问题吗？该数据库中可能有更好的方法来创建虚拟 Nums 表。VALUES(5,"Fraser",4);

1赞 ar ia 11/14/2023

亲爱的肖恩。您的代码已奏效。我只是做了一个轻微的调整，它为我创造了奇迹。我的代码大大缩短了。非常感谢你给了我一种处理此类项目的新方法。现在，我可以通过结合您的技术来加入任意数量的表。再次非常感谢你。

上一个：在langchain实现中，SQLDatabaseToolkit出错：SQLDatabase的数据库实例预期。这是连接问题吗？

下一个：合并两个具有精确数据分布的表

联接多个表以显示整个数据，而无需交叉联接

Joining multiple tables to display entire data without cross joining

评论

评论

结构 ID	跨度编号
5	1
5	2
5	3
5	4
6	1
6	2
6	3
6	4
6	5
6	6
7	1
7	2
7	3
8	1
8	2
8	3
8	4
8	5
9	1
9	2
9	3
9	4
9	5
9	6
10	1
10	2
10	3
10	4
10	5

结构 ID	跨度编号
5	1
5	2
5	3
5	4
6	1
6	2
6	3
6	4
6	5
6	6
7	1
7	2
7	3
8	1
8	2
8	3
8	4
8	5
9	1
9	2
9	3
9	4
9	5
9	6
10	1
10	2
10	3
10	4
10	5

结构 ID	跨度编号
5	1
5	2
5	3
5	4
6	1
6	2
6	3
6	4
6	5
6	6
7	1
7	2
7	3
8	1
8	2
8	3
8	4
8	5
9	1
9	2
9	3
9	4
9	5
9	6
10	1
10	2
10	3
10	4
10	5