查找谁拥有Sql Server列中的所有确切值

Question

我已经添加了db脚本与数据。

我需要从表中获取记录"providers"其值可在&;providersavl&;表格通过确保来自两个表的值不在第三个表中";providerbreak";(where SQL not in)

我需要使用查询

得到以下结果

DECLARE @InString VARCHAR(50)= '10,11,12,13';
DECLARE @InTemp TABLE(KEYY TINYINT);
INSERT @InTemp(KEYY)
SELECT VALUE FROM STRING_SPLIT(@InString,',')

SELECT X.PROVIDERID,X.VAL FROM
(
SELECT P.PROVIDERID,P.val,
COUNT(*)OVER (PARTITION BY P.PROVIDERID)XCOL
FROM DBO.PROVIDERAVL AS P

JOIN @InTemp T ON P.val=T.KEYY
WHERE T.KEYY NOT IN 
(SELECT VAL FROM PROVIDERBREAK) 

)X WHERE X.XCOL=(SELECT COUNT(*)FROM @InTemp)

ProviderId   VAL
3            10
3            11
3            12
3            13

返回空结果

有谁能帮帮我吗?

GO
/****** Object:  Table [dbo].[provideravl]    Script Date: 6/3/2022 10:41:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[provideravl](
[id] [int] IDENTITY(1,1) NOT NULL,
[providerid] [int] NULL,
[val] [int] NULL,
CONSTRAINT [PK_provideravl] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[providerbreak]    Script Date: 6/3/2022 10:41:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[providerbreak](
[id] [int] IDENTITY(1,1) NOT NULL,
[providerid] [int] NULL,
[val] [int] NULL,
CONSTRAINT [PK_providerbreak] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[providers]    Script Date: 6/3/2022 10:41:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[providers](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[val] [int] NULL,
CONSTRAINT [PK_providers] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[provideravl] ON 
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (1, 1, 1)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (2, 1, 2)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (3, 1, 3)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (4, 1, 4)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (5, 1, 5)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (6, 1, 6)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (7, 1, 7)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (8, 1, 8)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (9, 1, 9)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (10, 1, 10)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (11, 2, 5)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (12, 2, 6)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (13, 2, 7)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (14, 2, 8)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (15, 2, 9)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (16, 2, 10)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (17, 2, 11)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (18, 2, 12)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (19, 2, 13)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (20, 2, 14)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (21, 2, 15)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (22, 2, 16)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (23, 2, 17)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (24, 2, 18)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (25, 2, 19)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (26, 2, 20)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (27, 3, 9)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (28, 3, 10)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (29, 3, 11)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (30, 3, 12)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (31, 3, 13)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (32, 3, 14)
GO
INSERT [dbo].[provideravl] ([id], [providerid], [val]) VALUES (33, 3, 15)
GO
SET IDENTITY_INSERT [dbo].[provideravl] OFF
GO
SET IDENTITY_INSERT [dbo].[providerbreak] ON 
GO
INSERT [dbo].[providerbreak] ([id], [providerid], [val]) VALUES (1, 2, 10)
GO
SET IDENTITY_INSERT [dbo].[providerbreak] OFF
GO
SET IDENTITY_INSERT [dbo].[providers] ON 
GO
INSERT [dbo].[providers] ([id], [name], [val]) VALUES (1, N'Asif', 1)
GO
INSERT [dbo].[providers] ([id], [name], [val]) VALUES (2, N'Sajid', 2)
GO
INSERT [dbo].[providers] ([id], [name], [val]) VALUES (21, N'Qasim', 3)
GO
SET IDENTITY_INSERT [dbo].[providers] OFF
GO
USE [master]
GO
ALTER DATABASE [TEST] SET  READ_WRITE 
GO

Answer 1

DECLARE @InString VARCHAR(50)= '9,10,11,12,13';
DECLARE @InTemp TABLE(KEYY TINYINT);
INSERT @InTemp(KEYY)
SELECT VALUE FROM STRING_SPLIT(@InString,',')

SELECT X.NAME,X.VAL FROM
(
SELECT P.NAME,P.val,
COUNT(*)OVER (PARTITION BY P.NAME)XCOL
FROM DBO.PROVIDERS AS P
JOIN @InTemp T ON P.val=T.KEYY
)X WHERE X.XCOL=(SELECT COUNT(*)FROM @InTemp)

我希望它能被简化。