连接两个不同行数的表(在sql server中) [英] join two tables with different number of rows(in sql server)

问题描述

我有两个表:A(col1,col2,col3,col4)

I have two tables: A(col1,col2,col3,col4)

B(col1,col2,col3,col4)

B(col1,col2,col3,col4)

表A有4条记录(行)，B有6行.我想像这样加入它们，例如将它们加入C强>表

Table A has 4 records(rows) and B has 6 rows.I want to join them like this,for example join them in C table

C(B.col1,B.col2,A.col3,A.col4,B.col3,B.col4)(表在记录中有不同的值，只是 col1 和 col2 包含相同的值)

C(B.col1,B.col2,A.col3,A.col4,B.col3,B.col4) (tables have different values in records just col1 and col2 contains the same values)

当我在 A.col1=B.col1 和 A.col2=B.col2 上加入他们时，我采用笛卡尔积:(

when i join them on A.col1=B.col1 and A.col2=B.col2 I take Cartesian product :(

PS 我想在 C 中有 6 行，其中 B.col1、B.col2、B.col3、B.col4 有 6 行，A.col3、A.col4 有 4 行和其他 2 空

P.S I want to have 6 rows in C,where B.col1,B.col2,B.col3,B.col4 have 6rows and A.col3,A.col4 have 4 rows and other 2 null

请帮帮我..

推荐答案

你需要使用FULL OUTER JOIN

Select a.Col1,
       a.Col2,
       a.Col3,
       a.Col4,
       b.Col3,
       b.Col4

From TableA a
Full Outer Join TableB b on a.Col1 = b.Col1
                         And a.Col2 = b.Col2

不起作用"不会帮助任何人.也许您可以提供有关为什么它不适合您的更多详细信息.

"doesn't work" is not going to help anyone. Maybe you could provide further details as to why it isn't working for you.

您可以在问题中添加一些示例数据和实际预期输出.

You could add some example data and the actual expected output to your question.

例如

Create Table #TableA
(
Col1 
...
)

Insert Into #TableA
Values (...)

你的问题越清楚，答案就会越好.如果人们不完全了解您的确切问题是什么以及您想要的预期输出，那么我们应该如何为您提供完整和正确的答案.

The clearer your question then the better the answers will be. If people don't fully understand what your exact problem is and expected output you want then how are we supposed to provide you with a full and correct answer.

这篇关于连接两个不同行数的表(在sql server中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！