SQL动态透视场景下筛选同时满足多动态问答条件客户的技术求助

嘿，我完全懂你现在卡在哪了——你已经搞定了动态问答转列的透视逻辑，但现在需要筛选同时满足多个问答条件的客户，而不是满足任一条件的。当前用OR会把只符合其中一个条件的客户也拉进来，这显然不是你要的效果。

核心问题在于：DynamicAnswers表里同一个客户的不同答案是存在不同行的，直接用AND连接两个条件会因为单行没法同时满足两个不同的问答要求，导致啥结果都查不出来。所以得换个思路：先找出那些同时符合所有条件的客户，再把这些客户的数据拿去做透视。

两种可行解决方案 #

方案1：分组统计满足条件的记录数

先筛选出符合任一目标条件的记录，然后按客户（InvoiceNum）分组，只保留满足条件的记录数等于你要求的条件数量的客户（比如这里是2个条件，就筛选COUNT(DISTINCT DynamicQuestions.ControlID) = 2）。

修改后的完整SQL如下：

DECLARE @docDates VARCHAR(MAX)

SELECT @docDates = STUFF((
    SELECT DISTINCT ', ' + QUOTENAME(CONVERT(varchar, REPLACE(QAPanelName,'QA_','')) + CONVERT(varchar, LEN(Orderbynumber)) + CONVERT(varchar, Orderbynumber) + CONVERT(varchar, ControlID) + '_' + QuestionColumnName)
    FROM DynamicAnswers RIGHT OUTER JOIN DynamicQuestions ON DynamicAnswers.FK_Answers_ControlID = DynamicQuestions.ControlID
    WHERE FK_DynamicQuestions_Topic = 56
    ORDER BY ', '  + QUOTENAME(CONVERT(varchar, REPLACE(QAPanelName,'QA_','')) + CONVERT(varchar, LEN(Orderbynumber)) + CONVERT(varchar, Orderbynumber) + CONVERT(varchar, ControlID) + '_' + QuestionColumnName)
    FOR XML PATH('')
), 1, 1, '')

DECLARE @sql NVARCHAR(MAX)

SET @sql = '
WITH QualifiedCustomers AS (
    SELECT da.FK_Answers_customer
    FROM DynamicAnswers da
    JOIN DynamicQuestions dq ON da.FK_Answers_ControlID = dq.ControlID
    WHERE dq.FK_DynamicQuestions_Topic = 56
    AND (
        (dq.ControlID = 42 AND da.Answer = ''30'')
        OR (dq.ControlID = 43 AND da.Answer = ''Texas'')
    )
    GROUP BY da.FK_Answers_customer
    -- 这里的数字要和你需要满足的条件数量一致，比如2个条件就填2
    HAVING COUNT(DISTINCT dq.ControlID) = 2
)
SELECT DISTINCT * 
FROM (
    SELECT  
        c.Country,
        c.eMail,
        c.ShortTalk,
        c.InvoiceNum, 
        CONVERT(varchar, REPLACE(dq.QAPanelName,''QA_'','''')) + CONVERT(varchar, LEN(dq.Orderbynumber)) + CONVERT(varchar, dq.Orderbynumber) + CONVERT(varchar, dq.ControlID) + ''_'' + dq.QuestionColumnName AS FK_Answers_ControlID, 
        da.Answer
    FROM customer c
    LEFT JOIN DynamicAnswers da ON da.FK_Answers_customer = c.InvoiceNum
    LEFT JOIN DynamicQuestions dq ON dq.ControlID = da.FK_Answers_ControlID
    WHERE c.Deleted = 0 
    AND c.SubmissionConfirmed = 1 
    AND c.FK_customer_Topic = 56  
    AND c.Cancelled = 0
    AND dq.FK_DynamicQuestions_Topic = 56
    -- 只保留符合所有条件的客户
    AND c.InvoiceNum IN (SELECT FK_Answers_customer FROM QualifiedCustomers)
) AS[SubTable]
PIVOT(
    MAX(Answer)
    FOR[FK_Answers_ControlID] IN(' + @docDates + ')
) AS[Pivot]; '

EXEC sp_executesql @sql

方案2：使用多个EXISTS子查询

如果你的条件数量不多，用多个EXISTS分别验证客户是否满足每个条件会更直观，逻辑也更容易理解：

修改后的主查询部分如下：

SET @sql = '
SELECT DISTINCT * 
FROM (
    SELECT  
        c.Country,
        c.eMail,
        c.ShortTalk,
        c.InvoiceNum, 
        CONVERT(varchar, REPLACE(dq.QAPanelName,''QA_'','''')) + CONVERT(varchar, LEN(dq.Orderbynumber)) + CONVERT(varchar, dq.Orderbynumber) + CONVERT(varchar, dq.ControlID) + ''_'' + dq.QuestionColumnName AS FK_Answers_ControlID, 
        da.Answer
    FROM customer c
    LEFT JOIN DynamicAnswers da ON da.FK_Answers_customer = c.InvoiceNum
    LEFT JOIN DynamicQuestions dq ON dq.ControlID = da.FK_Answers_ControlID
    WHERE c.Deleted = 0 
    AND c.SubmissionConfirmed = 1 
    AND c.FK_customer_Topic = 56  
    AND c.Cancelled = 0
    AND dq.FK_DynamicQuestions_Topic = 56
    -- 分别验证客户满足两个条件
    AND EXISTS (
        SELECT 1 FROM DynamicAnswers da1
        JOIN DynamicQuestions dq1 ON da1.FK_Answers_ControlID = dq1.ControlID
        WHERE da1.FK_Answers_customer = c.InvoiceNum
        AND dq1.ControlID = 42 AND da1.Answer = ''30''
    )
    AND EXISTS (
        SELECT 1 FROM DynamicAnswers da2
        JOIN DynamicQuestions dq2 ON da2.FK_Answers_ControlID = dq2.ControlID
        WHERE da2.FK_Answers_customer = c.InvoiceNum
        AND dq2.ControlID = 43 AND da2.Answer = ''Texas''
    )
) AS[SubTable]
PIVOT(
    MAX(Answer)
    FOR[FK_Answers_ControlID] IN(' + @docDates + ')
) AS[Pivot]; '

方案选择建议 #

• 方案1更适合条件数量较多的场景，只需要调整HAVING子句中的数字就能适配更多条件；
• 方案2逻辑更直白，适合条件少的情况，每个条件对应一个独立的验证逻辑，后期维护起来更轻松。

备注：内容来源于stack exchange，提问作者Meeting Bloom