时间戳

首页 > 解决方案 > 在 SQL MS Access 中连接两个相似的表

sql - 在 SQL MS Access 中连接两个相似的表

问题描述

我正在尝试加入两个表，但无法得到确切的结果。表 2 是根据默认逻辑计算需求表 1 是手动覆盖相同的数据。我需要 Table1 中的所有数据都是固定的，并且只有 table2 中的新行或不匹配的行才能添加到 Table1

select * from table1
left outer join table2 on table1.project = table2.project and table1.function = table2.function and table1.Month = table2.Month and table1.Phase = table2.Phase and table1.Complexity = table2.Complexity;

CREATE TABLE Table1
    (`Project` varchar(31), `function` varchar(8), `Month` datetime, `Phase` int, `Complexity` varchar(3), `Needed` float)
;

INSERT INTO Table1
    (`Project`, `function`, `Month`, `Phase`, `Complexity`, `Needed`)
VALUES
    ('3D Templates - Project DeLorean', 'Clinical', '2019-01-01 00:00:00', 3, 'Low', 0.2),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-02-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-03-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-04-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-05-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-06-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-07-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-08-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-09-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-10-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-11-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-12-01 00:00:00', 4, 'Low', 0.5)
;


CREATE TABLE Table2
    (`Project` varchar(31), `function` varchar(8), `Month` datetime, `Phase` int, `Complexity` varchar(6), `Needed` float)
;

INSERT INTO Table2
    (`Project`, `function`, `Month`, `Phase`, `Complexity`, `Needed`)
VALUES
    ('3D Templates - Project DeLorean', 'Clinical', '2019-01-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-02-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-03-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-04-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-05-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-06-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-07-01 00:00:00', 3, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-08-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-09-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-10-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-11-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'Clinical', '2019-12-01 00:00:00', 4, 'Low', 0.5),
    ('3D Templates - Project DeLorean', 'GSM', '2019-01-01 00:00:00', 3, 'Medium', 1.5)
;

注意表 1 和表 2 中的第一行。两者中所需的值不同，但我只需要 Table1 数据。表 2 中的最后一行是新添加的。我需要 Table1 中的那一行

标签： sqlms-access

解决方案

您可以使用union all. 目前尚不清楚您如何定义重复项。如果它位于前三列，则：

select t1.*
from table1 as t1
union all
select t2.*
from table2 as t2
where not exists (select 1
                  from table1 as t1
                  where t1.project = t2.project and
                        t1.function = t2.function and
                        t1.month = t2.month
                 );

如果它在所有列上，那么您只需要两个表中的所有行，您可以使用union：

select t1.*
from table1 as t1
union  -- on purpose to remove duplicates
select t2.*
from table2 as t2;

推荐阅读