SQL 交叉表查询 [英] SQL Cross Tab Query

问题描述

需要帮助弄清楚如何在一个查询中生成交叉表格报告.涉及 3-4 个表，但用户表可能不需要包含在查询中，因为我们只需要一个计数.

Need help figuring out how to do a cross-tabulated report within one query. There are 3-4 tables involved but the users table may not need to be included in the query since we just need a count.

我将表架构和数据的屏幕截图放在一起作为示例，如下所示:

I have put together a screenshot of the table schema and data as an example which can be seen below:

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

What I need it to return is a query result that looks like:

所以我可以制作一个看起来像这样的报告:

So I can make a report that looks like:

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

I've tried to do cursor loops as it's the only way I can do it with my basic knowledge, but it's way too slow.

我正在尝试生成的一份特定报告包含 32 行和 64 列，其中包含大约 70,000 个答案，因此这一切都与将其归结为一个查询并尽可能快的性能有关.

One particular report I'm trying to generate contains 32 rows and 64 columns with about 70,000 answers, so it's all about the performance of getting it down to one query and fast as possible.

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

I understand this may depend on indexes and so on but if someone could help me figure out how I could get this done in 1 query (with multiple joins?), that would be awesome!

谢谢！

推荐答案

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，因为 SQL Server 要求您指定将转换为列的实际值.

This should be at least close to what you asked for. Turning this into a pivot will require dynamic SQL as SQL Server requires you to specify the actual value that will be pivoted into a column.

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

We cross join the questions and limit the results from each of those question references to the single question for the row values and column values respectively. Then we join the option values to the respective question reference. We use LEFT JOIN for the answers in case the user didn't respond to all of the questions. And we join the answers by UserID so that we match the row question and column question for each user. The MIN on the option text is because we grouped and ordered by OptionID to match your sequencing shown.

这是一个 SQLFiddle

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

For what it's worth, your query is complicated because you are using the Entity-Attribute-Value design pattern. Quite a few SQL Server experts consider that pattern to be problematic and to be avoided if possible. For instance see https://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/.

编辑 2:既然你接受了我的回答，这里是动态 SQL 数据透视解决方案 :) SQLFiddle

EDIT 2: Since you accepted my answer, here's the dynamic SQL pivot 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

这篇关于SQL 交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！