加入两个SQL Server表[重复]

This question already has an answer here:

这个问题在这里已有答案:

• Combine two tables for one output 3 answers

将两个表合并为一个输出3个答案

I have two tables now I need a select or join command in SQL to have the third table just like image below

我现在有两个表我需要在SQL中使用select或join命令让第三个表就像下面的图像一样

My two tables are like this:

我的两个表是这样的:

I only know a simple things about join command in SQL, should I use join or something else?

我只知道SQL中关于join命令的一些简单的事情,我应该使用join还是其他什么?

I do not want have the third table in my database, I want that for a short time (something like virtual table). Please help !

我不希望在我的数据库中有第三个表,我希望在短时间内(类似于虚拟表)。请帮忙 !

4 个解决方案

#1

You are actually looking for UNION or UNION ALL.

你实际上在寻找UNION或UNION ALL。

First of all, there is no condition on which to JOIN tables (review your documentation on JOIN) and JOIN is used for retrieving information about one logical element, let's say Event in your case, which has details stored in more tables.

首先,JOIN表没有条件(在JOIN上查看你的文档),JOIN用于检索有关一个逻辑元素的信息,比如你的情况下的事件,其中有详细信息存储在更多的表中。

Secondly, JOIN will make one result set with all of the columns of your two tables, when actually you are not trying to get all columns, but all rows.

其次,JOIN将为您的两个表的所有列创建一个结果集,而实际上您并不是要获取所有列,而是所有行。

For this you will have to use UNION or UNION ALL like this:

为此,您必须使用UNION或UNION ALL,如下所示:

SELECT
    EventID,
    ID,
    EventName,
    Date,
    Pic,
    Privacy
FROM Table1

UNION ALL

SELECT
    PLID AS EventID,
    ID AS ID,
    PlaceName AS EventName,
    Date AS Date,
    NULL AS Pic,
    NULL AS Privacy
FROM Table2

In order to sort the result you get from the result set returned by the queries above you will need to wrap your above SELECT statements with another SELECT and use a WHERE clause at that level, like below:

为了对从上面的查询返回的结果集中获得的结果进行排序,您需要使用另一个SELECT包装上述SELECT语句并在该级别使用WHERE子句,如下所示:

SELECT *
FROM (SELECT
        EventID,
        ID,
        EventName,
        Date,
        Pic,
        Privacy
    FROM Table1

    UNION ALL

    SELECT
        PLID AS EventID,
        ID AS ID,
        PlaceName AS EventName,
        Date AS Date,
        NULL AS Pic,
        NULL AS Privacy
    FROM Table2) AS Result
WHERE Date > '2014-05-26'

#2

What you're looking to do is a UNION or UNION ALL, not a join. See: http://www.w3schools.com/sql/sql_union.asp

你要做的是UNION或UNION ALL,而不是连接。请参阅:http://www.w3schools.com/sql/sql_union.asp

UNION combines two tables without connecting their content. Your example shows all 4 records from the original tables unmodified.

UNION在不连接其内容的情况下组合了两个表。您的示例显示原始表中未修改的所有4条记录。

A JOIN solution links the two tables. It's very common and you will probably use it if you're building a relational database, but it won't give you the example result.

JOIN解决方案链接两个表。这很常见,如果您正在构建关系数据库,您可能会使用它,但它不会给您示例结果。

Since the two tables don't have identical # of columns, you have to help it out here:

由于这两个表没有相同的列数,因此您必须在此处提供帮助:

SELECT EventID, EventName, Date, Pic, privacy FROM [table 1]
UNION ALL
SELECT PLID, PlaceName, Date, null, null FROM [table 2]

#3

You want to have one table from two different tables. So you need unified result set from each by renaming column in SELECT statement:

您希望从两个不同的表中获得一个表。因此,您需要通过SELECT语句中的重命名列来统一每个结果集:

SELECT `EventID` AS `ObjectID`, `EventName` AS `ObjectName`, .... FROM table_1 ...

similary with table_2

与table_2相似

Then combine to one result set:

然后合并到一个结果集:

SELECT `ID` AS `ObjectID`, `EventName` AS `ObjectName`, .... FROM table_1 ...
UNION
SELECT `PlaceID` AS `ObjectID`, `PlaceName` AS `ObjectName`, .... FROM table_2 ...

#4

My mistake, I didn't take the time to examine the pictures fully. you would have to use Union since you want to return what is in both tables.

我的错误,我没有花时间完全检查图片。你必须使用Union,因为你想要返回两个表中的内容。