sql - 如何根据表之间的差异更新不同的列

Question

我有三个表，表1、表2和表3。表1记录了所有现有记录，表2记录了要应用于表1的增量(新更新)。

表 3 是结果表。

表1、表3结构:ID为主键ID、日期、地点、年龄、人数

表2结构:ID为主键ID、日期、位置、年龄、计数、ChangeType

表 2 仅记录表 1 中更改的字段的新更新值。

例如:

表一

1, 03/03/2017, A, 11, 1
2, 01/03/2017, B, 39, 1
3, 01/01/2017, D, 1, 1

表2

2, 03/03/2017,NULL, NULL,2, Update
1, NULL, CC, NULL, NULL, Update

因此表3应该是

1, 03/03/2017, CC, 11, 1
2, 03/03/2017, B, 39, 2
3, 01/01/2017, D, 1, 1

如有任何建议，我们将不胜感激。

GO
/****** Object:  Table [dbo].[Table_1]    Script Date: 03/03/2017 10:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
    [ID] [int] NOT NULL,
    [date] [date] NULL,
    [location] [nvarchar](50) NULL,
    [age] [int] NULL,
    [count] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Table_2]    Script Date: 03/03/2017 10:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
    [ID] [int] NOT NULL,
    [date] [date] NULL,
    [location] [nvarchar](50) NULL,
    [age] [int] NULL,
    [count] [int] NULL,
    [ChangeTyppe] [nvarchar](10) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Table_3]    Script Date: 03/03/2017 10:41:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
    [ID] [int] NOT NULL,
    [date] [date] NULL,
    [location] [nvarchar](50) NULL,
    [age] [int] NULL,
    [count] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Table_1] ([ID], [date], [location], [age], [count]) VALUES (1, CAST(0x863C0B00 AS Date), N'A', 11, 1)
GO
INSERT [dbo].[Table_1] ([ID], [date], [location], [age], [count]) VALUES (2, CAST(0x843C0B00 AS Date), N'B', 39, 1)
GO
INSERT [dbo].[Table_1] ([ID], [date], [location], [age], [count]) VALUES (3, CAST(0x493C0B00 AS Date), N'D', 1, 1)
GO
INSERT [dbo].[Table_2] ([ID], [date], [location], [age], [count], [ChangeTyppe]) VALUES (2, CAST(0x863C0B00 AS Date), NULL, NULL, 2, N'Update')
GO
INSERT [dbo].[Table_2] ([ID], [date], [location], [age], [count], [ChangeTyppe]) VALUES (1, NULL, N'CC', NULL, NULL, N'Update')
GO

Answer 1

如果您只需要一次性的东西，那么这应该可行。

Left Join 主键上的两个表，如果不为null则从T2中取值。如果 2 的值为 null，则取 T1 的值。

Coalesce 将返回第一个不为 null 的值。

如果该行在 T2 中不存在，或者如果 T2 中的值为 null - 每个 ID，则 T2 中的值将为 null。

INSERT dbo.Table_3
        ( ID, date, location, age, count )
SELECT t1.ID ,
       [date] = COALESCE(t2.date, t1.date) ,
       [location] = COALESCE(t2.location, t1.location) ,
       [age] = COALESCE(t2.age, t1.age) ,
       [count] = COALESCE(t2.count, t1.count)
FROM dbo.Table_1 t1
LEFT OUTER JOIN dbo.Table_2 t2 ON t2.ID = t1.ID