我在研究如何编写一个查询，能够显示某个成员是否符合我们指标表中定义的指标。我们有一张保存成员调查答案的表，并且根据这些答案是否符合某种模式来定义业务指标。

保存调查答案的表对一个成员来说可能长成下面这样的样子。CID是成员的内部ID。

这是指标表中的一个示例指标。

基于调查答案的最后三行，我可以看到这个成员符合指标1001的所有三个条件，因为表单ID、概念ID、问题ID，以及问题值都与指标表中的行匹配。我一直在尝试在SQL查询中将调查答案表与指标表连接起来，以便对满足指标表中所有行的成员，获取成员ID和指标ID。一直在尝试使用NOT EXISTS来查找满足所有行的成员，但卡在这里，非常感谢任何帮助。

为了便于演示，添加数据。

--Metrics Table
DROP TABLE IF EXISTS #METRIC_RULE;
CREATE TABLE #METRIC_RULE
(
    CONDITION_TYPE VARCHAR(50),
    METRIC_ID INT,
    SUB_ID VARCHAR(50),
    FORM_ID INT,
    CONCEPT_ID INT,
    QUESTION_ID INT,
    QUESTION_VALUE VARCHAR(50)
);


INSERT INTO #METRIC_RULE
(
    CONDITION_TYPE,
    METRIC_ID,
    SUB_ID,
    FORM_ID,
    CONCEPT_ID,
    QUESTION_ID,
    QUESTION_VALUE
)
VALUES
('AND', 1001, '001', 501919, 502659, 9, '2'),
('AND', 1001, '001', 501919, 500787, 15, '2'),
('AND', 1001, '001', 501919, 502494, 42, '1'),
('AND', 1001, '002', 599919, 599493, 12, '1'),
('AND', 1001, '002', 599919, 599494, 18, '1')

DROP TABLE IF EXISTS #SURVEY_ANSWERS;
CREATE TABLE #SURVEY_ANSWERS
(
    [CID] INT,
    [FORM_ID] INT,
    [CONCEPT_ID] INT,
    [QUESTION_ID] INT,
    [QUESTION_VALUE] INT
);

INSERT INTO #SURVEY_ANSWERS
(
    [CID],
    [FORM_ID],
    [CONCEPT_ID],
    [QUESTION_ID],
    [QUESTION_VALUE]
)
VALUES
(1234, 501783, 500120, 14, 1),
(1234, 501919, 500120, 16, 1),
(1234, 501833, 500120, 16, 1),
(1234, 501799, 500120, 16, 1),
(1234, 501919, 500120, 48, 1),
(1234, 501783, 500457, 8, 2),
(1234, 501926, 500457, 8, 2),
(1234, 501919, 500787, 15, 2),
(1234, 501919, 502494, 42, 1),
(1234, 501919, 502659, 9, 2);

我在Azure托管实例上的SSMS中工作。我的期望是SELECT能返回符合条件的CID、Metric ID和 Sub ID。所以上面的例子中，应该返回成员1234确实满足1001 001指标，但不满足1001 002指标。

解决方案

我认为你首先要把规则左连接到答案上，确保每个答案都会返回一行（尽管指标很可能总是存在，在那种情况下可以直接连接——从你的数据并不清楚）。

然后你需要对每个metric_id和 sub_id统计不正确的答案数量，用它来筛选出没有百分百正确的指标。

with cte as (
    select sa.CID, mr.METRIC_ID, mr.SUB_ID
        , case when sa.QUESTION_VALUE != mr.QUESTION_VALUE then 1 else 0 end IncorrectAnswer
    from #SURVEY_ANSWERS sa
    left join #METRIC_RULE mr on mr.FORM_ID = sa.FORM_ID and mr.CONCEPT_ID = sa.CONCEPT_ID and mr.QUESTION_ID = sa.QUESTION_ID
)
select CID, METRIC_ID, SUB_ID
from cte
where METRIC_ID is not null
group by CID, METRIC_ID, SUB_ID
having sum(IncorrectAnswer) = 0;

返回：

请注意，上面的示例就是你可以提供期望结果的方式，也是通常显示数据的方式。