SQL交叉选项卡查询

Question

需要帮助找出如何在一个查询中执行跨表报表。涉及3-4个表，但用户表可能不需要包含在查询中，因为我们只需要一个计数。

我将表、模式和数据的屏幕截图作为示例，如下所示：

​

我需要它返回的是一个如下所示的查询结果：

​

所以我可以做一份报告，看起来：

​

我试着做游标循环，因为这是我用基本知识可以做的唯一方法，但它太慢了。

我正在尝试生成的一个特定报告包含32行和64列，包含大约70000个答案，所以这都取决于将其简化为一个查询的性能以及尽可能快的速度。

我知道这可能取决于索引等等，但是如果有人能帮我弄清楚如何在1查询中完成(多个联接？)，那就太棒了！

谢谢!

Answer 1

SELECT MIN(ro.OptionText) RowOptionText, MIN(co.OptionText) RowOptionText, COUNT(ca.AnswerID) AnswerCount
FROM tblQuestions rq 
CROSS JOIN tblQuestions cq 
JOIN tblOptions ro ON rq.QuestionID = ro.QuestionID
JOIN tblOptions co ON cq.QuestionID = co.QuestionID
LEFT JOIN tblAnswers ra ON ra.OptionID = ro.OptionID
LEFT JOIN tblAnswers ca ON ca.OptionID = co.OptionID AND ca.UserID = ra.UserID
WHERE rq.questionText = 'Gender'
AND cq.questionText = 'How happy are you?'
GROUP BY ro.OptionID, co.OptionID
ORDER BY ro.OptionID, co.OptionID

这至少应该接近你的要求。将其转换为支点将需要动态SQL，因为Server要求您指定将被旋转到列中的实际值。

我们交叉连接这些问题，并将每个问题引用的结果分别限制为行值和列值的单个问题。然后将选项值加入到相应的问题引用中。如果用户没有回答所有的问题，我们使用左联接作为答案。我们通过UserID加入答案，以便为每个用户匹配行问题和列问题。选项文本上的最小值是因为我们按照OptionID进行分组和排序，以匹配您所显示的顺序。

编辑:这是一个SQLFiddle

至于它的价值，您的查询是复杂的，因为您使用的是实体属性值设计模式。相当多的Server专家认为这种模式是有问题的，如果可能的话应该避免。例如，请参见https://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/。

编辑2:既然您接受了我的答案，下面是动态solution解决方案:) SQLFiddle

DECLARE @SqlCmd NVARCHAR(MAX)

SELECT @SqlCmd = N'SELECT RowOptionText, ' + STUFF(
    (SELECT ', ' + QUOTENAME(o.OptionID) + ' AS ' + QUOTENAME(o.OptionText)
    FROM tblOptions o 
    WHERE o.QuestionID = cq.QuestionID
    FOR XML PATH ('')), 1, 2, '') + ', RowTotal AS [Row Total]
FROM (
    SELECT ro.OptionID RowOptionID, ro.OptionText RowOptionText, co.OptionID ColOptionID,
       ca.UserID, COUNT(ca.UserID) OVER (PARTITION BY ra.OptionID) AS RowTotal
    FROM tblOptions ro
    JOIN tblOptions co ON ro.QuestionID = ' + CAST(rq.QuestionID AS VARCHAR(10)) + 
    ' AND co.QuestionID = ' + CAST(cq.QuestionID AS VARCHAR(10)) + '
    LEFT JOIN tblAnswers ra ON ra.OptionID = ro.OptionID
    LEFT JOIN tblAnswers ca ON ca.OptionID = co.OptionID AND ca.UserID = ra.UserID
    UNION ALL 
    SELECT 999999, ''Column Total'' RowOptionText, co.OptionID ColOptionID,
       ca.UserID, COUNT(ca.UserID) OVER () AS RowTotal
    FROM tblOptions ro
    JOIN tblOptions co ON ro.QuestionID = ' + CAST(rq.QuestionID AS VARCHAR(10)) + 
    ' AND co.QuestionID = ' + CAST(cq.QuestionID AS VARCHAR(10)) + '
    LEFT JOIN tblAnswers ra ON ra.OptionID = ro.OptionID
    LEFT JOIN tblAnswers ca ON ca.OptionID = co.OptionID AND ca.UserID = ra.UserID
) t
PIVOT (COUNT(UserID) FOR ColOptionID IN (' + STUFF(
    (SELECT ', ' + QUOTENAME(o.OptionID) 
    FROM tblOptions o 
    WHERE o.QuestionID = cq.QuestionID
    FOR XML PATH ('')), 1, 2, '') + ')) p
ORDER BY RowOptionID'
FROM tblQuestions rq 
CROSS JOIN tblQuestions cq 
WHERE rq.questionText = 'Gender' 
AND cq.questionText = 'How happy are you?'

EXEC sp_executesql @SqlCmd

Answer 2

我想我看到问题了。我知道您不能修改模式，但是您需要一个概念表，用于交叉表信息，比如哪个questionID是rowHeader，哪个是colHeader。您可以在外部数据源中创建它，并与现有数据源连接，或者简单地对sql中的表值进行硬编码。

您需要有两个问题/选项/答案关系的实例，每个rowHeader和每个交叉表的colHeader都有一个实例。这两种关系是由userID加入的。

此版本有外部联接：sqlFiddle

这个版本没有crossTab表，只有硬编码的行和col问题‘t：sqlFiddleNoTbl

Answer 3

下面的混乱没有硬编码值，但无法显示计数为0的行。然而，对于您的报告来说，这可能仍然有效。

;with stepone as(

SELECT
    RANK() OVER(PARTITION BY a.UserId ORDER BY o.QuestionID) AS [temprank]
,   o.QuestionID AS [QID1]
,   o.OptionID AS [OID1]
,   same.QuestionID
,   same.OptionID
,   a.UserId AS [IDUser]
,   same.UserId
FROM
    tblAnswers a
    INNER JOIN
    tblOptions o
        ON a.OptionID = o.OptionID
    INNER JOIN
    tblQuestions q
        ON o.QuestionID = q.QuestionID
    INNER JOIN
    (
    SELECT
        a.AnswerID
    ,   a.OptionID
    ,   a.UserId
    ,   o.QuestionID    
    FROM
        tblAnswers a
        INNER JOIN
        tblOptions o
            ON a.OptionID = o.OptionID
    ) same
        ON a.UserId = same.UserId AND a.AnswerID <> same.AnswerID

)

, stepthree AS(
SELECT
    t.QID1, t.OID1, t.QuestionID, t.OptionID
,   COUNT(UserId) AS myCount
FROM 
    stepone t
WHERE t.temprank = 1
GROUP BY
    t.QID1, t.OID1, t.QuestionID, t.OptionID
)

SELECT
    o1.OptionText AS [RowTest]
,   o2.OptionText AS [ColumnText]
,   t.myCount AS [Count]

FROM
    stepthree t
    INNER JOIN tblOptions o1
        ON t.OID1 = o1.OptionID
    INNER JOIN tblOptions o2
        ON t.OptionID = o2.OptionID
ORDER BY t.OID1

希望它能帮上忙，我喜欢尝试。