时间戳

首页 > 解决方案 > T-SQL：我可以查询一个视图的值，然后只查询第二个视图中不存在的值吗？

sql-server - T-SQL：我可以查询一个视图的值，然后只查询第二个视图中不存在的值吗？

问题描述

DROP TABLE IF EXISTS ..Players
DROP TABLE IF EXISTS ..FreeAgents

CREATE TABLE [dbo].[FreeAgents](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Position] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_FreeAgents] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Players](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Position] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Players] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[FreeAgents] ON 
GO
INSERT [dbo].[FreeAgents] ([Id], [FirstName], [LastName], [Position]) VALUES (2, N'Julian', N'Edelman', N'WideReceiver')
GO
INSERT [dbo].[FreeAgents] ([Id], [FirstName], [LastName], [Position]) VALUES (3, N'Dez', N'Bryant', N'WideReceiver')
GO
INSERT [dbo].[FreeAgents] ([Id], [FirstName], [LastName], [Position]) VALUES (4, N'Brandon', N'Jacobs', N'DefensiveEnd')
GO
SET IDENTITY_INSERT [dbo].[FreeAgents] OFF
GO
SET IDENTITY_INSERT [dbo].[Players] ON 
GO
INSERT [dbo].[Players] ([Id], [FirstName], [LastName], [Position]) VALUES (1, N'Tom', N'Brady', N'Quarterback')
GO
INSERT [dbo].[Players] ([Id], [FirstName], [LastName], [Position]) VALUES (2, N'Cam', N'Newton', N'Quarterback')
GO
INSERT [dbo].[Players] ([Id], [FirstName], [LastName], [Position]) VALUES (3, N'Julian', N'Edelman', N'WideReceiver')
GO
SET IDENTITY_INSERT [dbo].[Players] OFF
GO

SELECT FirstName, LastName, Position FROM ..Players
UNION
SELECT FirstName, LastName, Position FROM ..FreeAgents

上面的代码返回五行。这是对的。

名 姓 位置
布兰登 雅各布斯 防守端
凸轮 牛顿 四分卫
德兹 科比 宽接收器
朱利安 爱德曼 宽接收器
汤姆 布雷迪 四分卫

我的问题：

标签： sql-servertsqlviewduplicatesunion

解决方案

如果您有一些不是计算密集型的键集（例如您的示例中的 id col），我认为您最好的选择是使用临时表来存储第一个视图的结果，然后将其与第二个视图合并，其中检查它没有存在于临时表中。

SELECT * FROM view1
INTO #v1;

SELECT * FROM #v1
UNITON ALL
SELECT * FROM view2
WHERE NOT EXISTS(SELECT 1 FROM #v1 WHERE #v1.id = view2.id)

第二个视图应该只对丢失的记录执行计算。

使用WITH语句可能会更好，但我不确定 - 这需要在真实数据上进行测试。

WITH v1 AS (SELECT * FROM view1)
SELECT * FROM v1
UNION ALL
SELECT * FROM view2
WHERE NOT EXISTS (
  SELECT 1 FROM v1
  WHERE v1.id = view2.id -- Set duplicate condition here
)

推荐阅读