根据数据驱动条件从另一个表中查找数据("At least One"和"Required")

Question

以以下格式的数据为例，其中表B存储订单，表A存储可对B中的订单应用促销折扣的条件：

DROP TABLE #A
DROP TABLE #B   
-- Table #A stores information about the requirements for allowing promotions
CREATE TABLE #A(
PromoName varchar(50),
Product varchar(250),
ConditionType varchar(50)
)
INSERT INTO #A VALUES ('PromoA','Product1','AT LEAST ONE')
INSERT INTO #A VALUES ('PromoA','Product2','AT LEAST ONE')
INSERT INTO #A VALUES ('PromoA','Product3','REQUIRED')
INSERT INTO #A VALUES ('PromoA','Product4','REQUIRED')
INSERT INTO #A VALUES ('PromoA','Product5','REQUIRED')
-- Table B contains order information, and whether products from #A are in the order
CREATE TABLE #B(
QuoteID varchar(50),
ProductName varchar(250)
)
INSERT INTO #B VALUES ('Quote1','Product1')
INSERT INTO #B VALUES ('Quote2','Product3')
INSERT INTO #B VALUES ('Quote3','Product4')
INSERT INTO #B VALUES ('Quote4','Product5')
-- Select * from #A
-- Select * from #B

我需要在#B中找到与#A中设置的要求相匹配的数据。因此，在我提供的示例数据中，应该返回#B中的记录，因为满足了A的要求。。。即#B中的次序包含"；至少一个"；或"；产品1"；或"；产品2"；(因为#B确实包含产品1(并且它还包含所有的"；REQUIRED"；产品3、产品4和产品5的项。

但是，如果缺少一个必需的字段，比如我们要从#B中删除Product5，那么就不应该从表#B中返回任何记录。同样，如果表#B既不包含产品1也不包含产品2，则不应返回任何记录。

如何获取这些数据？我在这里尝试了一些在逻辑上对我来说似乎正确的东西，但事实并非如此，我认为这可能会变得比我最初想象的更复杂。这是我的代码：

;WITH CTE_Required as --These are "required" promotion requirements indicating that an item must be 
-- on the order
(
Select PromoName,Product,ConditionType from #A 
where ConditionType = 'REQUIRED'
),
CTE_AtLeastOne as --These are requirements that "at least one" of the "at least one" items must exist 
-- on the order
(
Select PromoName,Product,ConditionType from #A
where ConditionType = 'AT LEAST ONE'
),
CTE_PromoRequiredRestrictionNotMet as -- The "required" restriction test has failed for these
(
Select a.Product
from CTE_Required a
left join #B b on b.ProductName = a.Product
where b.QuoteID is null -- Data is in the "required" list but it's not in #B
),
CTE_PromoAtLeastOneRestrictionNotMet as --This data needs at least one in #B, but none exist in #B
(
Select a.Product
from CTE_AtLeastOne a
left join #B b on b.ProductName = a.Product
where b.QuoteID is not null 
),
CTE_PromoRequiredRestrictionMet as --These are items not in the failed items ("required" test passes)
(
Select * from #B where ProductName not in 
(
Select * from CTE_PromoRequiredRestrictionNotMet
) 
),
CTE_PromoAtLeastOneRestrictionMet as -- These pass the "At least one" test 
(
Select * from #B where ProductName not in 
(
Select * from CTE_PromoRequiredRestrictionNotMet
) 
)
Select * from CTE_PromoRequiredRestrictionMet c -- Get items that passed both tests
join CTE_PromoAtLeastOneRestrictionMet d on c.ProductName = d.ProductName

当产品/条件匹配时(在上面列出的数据示例中(，这将正确地返回所有记录，但是如果我删除"；"必需"；来自#B的产品。因此，如果我从#B中删除产品3，那么结果仍然返回产品1、2和4，这是我不想要的。我只想返回所有条件都满足的记录。

在我的代码中，我有点明白为什么它不起作用。。。我设置了几个CTE来获得小比特的数据(我试图区分满足"REQUIRED"要求的数据和分别满足"至少一个"要求的信息，然后找到同时满足这两个要求的信息。(还有一些中间CTE使用左联接来查找#A中哪些东西不在#B中，然后我将其与"不在"一起使用来决定#A和#B中的内容(。我有一种感觉，我需要在某个地方按条款分组。

在任何情况下，根据是否满足#A中定义的所有数据驱动条件，我可以使用什么查询从#B中选择全部记录或不选择记录？除了包含传递数据项的CTE末尾的内部联接外，我还尝试了一些不同的联接，包括联合，但没有一个完全有效。提前谢谢！

编辑：为了清楚起见，可能有更多的数据，所以在表#A中我有"；promoA"；列出，但也可能有促销B，我不希望促销a的结果影响促销B的结果。

Answer 1

这可能作为存储过程最有效，但请参阅下面的逻辑。

--DROP #C IF EXISTS
IF OBJECT_ID('tempdb..#C') IS NOT NULL DROP TABLE #C
--INSERT ALL OF #B INTO WORKING TABLE #C
SELECT * INTO #C FROM #B
--IF A REQUIRED PRODUCT IS MISSING, DELETE RECORDS FROM #C
IF EXISTS (SELECT * FROM #A A LEFT JOIN #C C ON A.PRODUCT = C.PRODUCTNAME WHERE A.CONDITIONTYPE ='REQUIRED' AND C.PRODUCTNAME IS NULL) DELETE FROM #C
--IF ANY 'AT LEAST ONE' PRODUCTS ARE MISSING, DELETE RECORDS FROM $C
IF NOT EXISTS(SELECT * FROM #C C WHERE C.PRODUCTNAME IN (SELECT PRODUCT FROM #A A WHERE A.CONDITIONTYPE ='AT LEAST ONE')) DELETE FROM #C
--RETURN RECORDS (IF ANY)
SELECT * FROM #C

• 约翰