具有不同列的T-SQL联合

I have two tables with the following data:

我有两个包含以下数据的表:

[Animals].[Males]
DataID                               HerdNumber HerdID  NaabCode
e46fff54-a784-46ed-9a7f-4c81e649e6a0 4          'GOLDA' '7JE1067'
fee3e66b-7248-44dd-8670-791a6daa5d49 1          '35'    NULL

[Animals].[Females]
DataID                               HerdNumber HerdID   BangsNumber
987110c6-c938-43a7-a5db-194ce2162a20 1          '9'      'NB3829483909488'
1fc83693-9b8a-4054-9d79-fbd66ee99091 2          'NATTIE' 'ID2314843985499'

I want to merge these tables into a view that looks like this:

我想将这些表合并到一个如下所示的视图中:

DataID                               HerdNumber HerdID   NaabCode  BangsNumber
e46fff54-a784-46ed-9a7f-4c81e649e6a0 4          'GOLDA'  '7JE1067' NULL
fee3e66b-7248-44dd-8670-791a6daa5d49 1          '35'     NULL      NULL
987110c6-c938-43a7-a5db-194ce2162a20 1          '9'      NULL      'NB3829483909488'
1fc83693-9b8a-4054-9d79-fbd66ee99091 2          'NATTIE' NULL      'ID2314843985499'`

When I used the UNION keyword, SQL Server produced a view that merged the NaabCode and BangsNumber into one column. A book that I have on regular SQL suggested UNION CORRESPONDING syntax like so:

当我使用UNION关键字时,SQL Server生成了一个将NaabCode和BangsNumber合并为一列的视图。我在常规SQL上的一本书建议使用UNION CORRESPONDING语法:

SELECT *
FROM [Animals].[Males]
UNION CORRESPONDING (DataID, HerdNumber, HerdID)
SELECT *
FROM [Animals].[Females]`

But when I type this SQL Server says "Incorrect syntax near 'CORRESPONDING'."

但是,当我键入此SQL Server时,“CORRESPONDING'附近的语法不正确。”

Can anyone tell me how to achieve my desired result and/or how to use UNION CORRESPONDING in T-SQL?

谁能告诉我如何实现我想要的结果和/或如何在T-SQL中使用UNION CORRESPONDING?

4 个解决方案

#1

You can just do:

你可以这样做:

SELECT DataID, HerdNumber, HerdID, NaabCode, NULL as BangsNumber
FROM [Animals].[Males]
UNION ALL
SELECT DataID, HerdNumber, HerdID, NULL as NaabCode, BangsNumber
FROM [Animals].[Females]

SQL Fiddle

I don't remember that SQL Server supports the corresponding syntax, but I might be wrong.
Anyway, this query will select null for the BangsNumber column for the males, and for the NaabCode column for the females, while selecting everything else correctly.

我不记得SQL Server支持相应的语法,但我可能错了。无论如何,此查询将为男性的BangsNumber列选择null,为女性选择NaabCode列,同时正确选择其他所有内容。

#2

Just do the union explicitly listing the columns:

只需明确列出列的联合:

select DataID, HerdNumber, HerdID, NaabCode, NULL as BangsNumber
from Animals.Males
union all
select DataID, HerdNumber, HerdID, NULL, BangsNumber
from Animals.Females;

Note: you should use union all instead of union (assuming that no single animal is both male and female). union incurs a performance overhead to remove duplicates.

注意:你应该使用union all而不是union(假设没有单个动物是男性和女性)。 union会导致性能开销以删除重复项。

#3

SELECT DataID, HerdNumber, HerdID, NaabCode, '' asBangsNumber
  FROM [Animals].[Males] 
UNION ALL
 SELECT DataID, HerdNumber, HerdID, '' as NaabCode, BangsNumber
  FROM [Animals].[Females]

#4

You need to state the columns in each select

您需要在每个选择中说明列

SELECT DataID, HerdNumber, HerdID
FROM [Animals].[Males]
UNION 
SELECT DataID, HerdNumber, HerdID
FROM [Animals].[Females]

#1