查找誰在SqlServer的列中具有所有確切值-有解無憂

我已經添加了帶有資料的 db 腳本。

我需要從表“providers”中獲取記錄，其值在“providersavl”表中可用，方法是確保兩個表中的值不存在于第三個表“providerbreak”（其中 SQL 不在）中

我需要使用查詢得到以下結果

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

uj5u.com熱心網友回復：

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)

我希望它可以以某種方式簡化

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/487001.html

標籤：sql服务器数据库

上一篇：使用SQL合并連續的日期范圍

下一篇：如何將字串列轉換為日期列