基于两列特定行条件获取第三列CacheUID值的实现方案

2023年3月6日更新：补充更多信息，并根据论坛反馈提供解决方案，感谢大家！

我需要基于FilterCriteriaName和CriteriaValue的行组合，筛选出对应的CacheUID值。以下是测试数据：

场景1 #

筛选条件：同时包含Product 4和Product 6，且仅包含这两个Product条件，无其他类型的筛选条件。

• 符合条件的CacheUID：2（CacheUID 2、3、5都包含Product 4和6，但只有2没有额外的Product或Equipment条件）

预期结果：

场景2 #

筛选条件：同时包含Product 4、Product 6以及Equipment 6，且Product类型仅包含4和6。

• 符合条件的CacheUID：5（只有5同时满足所有条件）

预期结果：

以下是生成测试数据及对应场景解决方案的SQL代码：

CREATE TABLE #TestTable(
    CacheUID [bigint] NOT NULL,
    FilterCriteriaName VARCHAR(50) NOT NULL,
    CriteriaValue [bigint] NOT NULL,
    UID [bigint] IDENTITY(1,1) NOT NULL
)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 1, 4)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 2, 4)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 2, 6)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 3, 4)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 3, 6)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 3, 8)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Equipment', 4, 16)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Equipment', 4, 3)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Equipment', 5, 4)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Equipment', 5, 6)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 5, 6)
INSERT  #TestTable (FilterCriteriaName, CacheUID, CriteriaValue)
VALUES  ('Product', 5, 4)

SELECT  CacheUID, FilterCriteriaName, CriteriaValue
FROM    #TestTable

-------------------------------------------------
-- 场景1: 筛选同时包含Product 4和6，且无其他条件的记录
-------------------------------------------------
SELECT      '场景1 - 仅包含Product 4和6', CacheUID
FROM        #TestTable
GROUP BY    CacheUID
HAVING      COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 4 THEN 1 END) > 0 AND
            -- 存在Product 4
            COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 6 THEN 1 END) > 0 AND
            -- 存在Product 6
            COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue NOT IN (4, 6) THEN 1 END) = 0 AND
            -- 仅包含Product 4或6
            COUNT(CASE WHEN FilterCriteriaName = 'Equipment' THEN 1 END) = 0
            -- 无Equipment类型条件

-----------------------------------------------------------------------
-- 场景2: 筛选同时包含Product 4、6和Equipment 6，且Product仅含4和6的记录
-----------------------------------------------------------------------
SELECT      '场景2 - 仅包含Product 4、6且包含Equipment 6', CacheUID
FROM        #TestTable
GROUP BY    CacheUID
HAVING      COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 4 THEN 1 END) > 0 AND
            -- 存在Product 4
            COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue = 6 THEN 1 END) > 0 AND
            -- 存在Product 6
            COUNT(CASE WHEN FilterCriteriaName = 'Product' AND CriteriaValue NOT IN (4, 6) THEN 1 END) = 0 AND
            -- 仅包含Product 4或6
            COUNT(CASE WHEN FilterCriteriaName = 'Equipment' AND CriteriaValue = 6 THEN 1 END) = 1
            -- 存在Equipment 6

DROP TABLE #TestTable

内容的提问来源于stack exchange，提问作者user21327340