SQL比较表并返回位数组或CSV

可能你想用“比特掩码”来形容你想要的东西。

其工作原理是建立一个包含所有可能的人和答案组合的列表,将其与实际答案相连接,然后使用递归CTE将结果连接到一行中:

DECLARE @tblPeopleAnswers TABLE
(PersonID INT 
,QuestionID INT
,AnswerID INT
)

INSERT @tblPeopleAnswers
VALUES (15,5,1),
(15, 5, 3),
(17, 5, 1),
(17, 5, 2)

DECLARE @tblAnswers TABLE
(QuestionID INT
,AnswerID INT
)

INSERT @tblAnswers
VALUES
(5,1),
(5,2),
(5,3)

;WITH ansCTE
AS
(
    SELECT answers.PersonID,
            answers.QuestionId,
            answers.AnswerId,
            CASE WHEN tpa.PersonID IS NULL 
                THEN '0'
                ELSE '1'
            END AS RESULT,
            ROW_NUMBER() OVER (PARTITION BY answers.PersonId, answers.QuestionId
                                ORDER BY    answers.AnswerId DESC
                                ) AS rn

    FROM (SELECT * FROM 
            (SELECT DISTINCT PersonID FROM @tblPeopleAnswers) AS z -- you may have @tblPeople you could use here??
            CROSS JOIN @tblAnswers
            ) AS answers
    LEFT JOIN @tblPeopleAnswers AS tpa
    ON tpa.QuestionID = answers.QuestionID
    AND tpa.AnswerId = answers.AnswerId
    AND tpa.PersonID = answers.PersonID
)
,recCTE
AS
(
    SELECT PersonId,
            QuestionId,
            AnswerId,
            CAST (RESULT AS VARCHAR(MAX)) AS BitAnswer,
            rn
    FROM ansCTE WHERE AnswerID = 1
    UNION ALL
    SELECT r.PersonId,
           r.QuestionId,
           a.AnswerID,
           r.BitAnswer + CAST(a.Result AS CHAR(1)),
           a.rn
    FROM recCTE AS r
    JOIN ansCTE AS a
    ON   a.PersonID = r.PersonID
    AND  a.QuestionID = r.QuestionID
    AND  a.AnswerID = r.AnswerID + 1
)
SELECT PersonId, 
       QuestionId,
       BitAnswer
FROM recCTE
WHERE rn = 1
ORDER BY PersonID, QuestionID