连接多个表并创建一个表

Question

​

​

我有三张桌子，叫做预付款，运输和医药。我想将这三个表连接起来，并为emp_id 1提供一个类似于最后一个的表。是否可以使用dql (原则)或sql在sql级别上这样做？

Answer 1

CREATE TABLE NewTable
SELECT  Date, 
        Emp_ID,
        Amount As Advance, 
        NULL AS Transport,
        NULL AS Medicine
FROM    Advance
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        Amount AS Transport,
        NULL AS Medicine
FROM    Transport
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        NULL AS Transport,
        Amount AS Medicine
FROM    Medicine
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID

• 创建TABLE...SELECT语法

您也可以在此创建一个VIEW，

CREATE VIEW EmployeeView
AS
SELECT  Date, 
        Emp_ID,
        Amount As Advance, 
        NULL AS Transport,
        NULL AS Medicine
FROM    Advance
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        Amount AS Transport,
        NULL AS Medicine
FROM    Transport
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID
UNION   ALL
SELECT  Date, 
        Emp_ID,
        NULL As Advance, 
        NULL AS Transport,
        Amount AS Medicine
FROM    Medicine
WHERE   Emp_ID = 1           -- remove this WHERE clause to include all emp_ID

• 创建视图语法