MySql - 通过将行转置为列来生成报告的 Sql 查询或过程

Question

这是第一次 - 我正在尝试做这样的事情 - 所以请耐心等待。这是在 MySql 上的。

我正在尝试生成一份报告，以查看哪些学生在哪些日期完成了哪些主题。

这是我当前运行的查询

select u.email,t.topic_name,tu.date_created as 'date completed'from topic_user tujoin topic t ON tu.topic_id = t.topic_idjoin user u ON tu.user_id = u.user_id

这将返回类似的结果

email         |    topic_name      |    date completedabc@gmail.com |    ABC             |    03/01/2012abc@gmail.com |    DEF             |    03/02/2012abc@gmail.com |    ABC             |    03/08/2012abc@gmail.com |    GHI             |    03/08/2012def@gmail.com |    ABC             |    03/02/2012def@gmail.com |    XYZ             |    03/10/2012

我想要生成报告的方式是将主题名称作为列标题，并将完成日期作为值

email         |    ABC      |    DEF      |    GHI      |    JKL      |    XYZ     abc@gmail.com | 03/08/2012  | 03/02/2012  | 03/08/2012  |    null     |    nulldef@gmail.com | 03/02/2012  |    null     |    null     |    null     |  03/10/2012

需要注意的几点是:

1) 所有主题名称都来自主题表 - 即使学生尚未完成 - 值应显示为 null

2) 以学生 abc@gmail.com 为例 - 他已经研究了 ABC 主题两次 - 但报告应该得到最新日期。

我想我必须编写一个存储过程来完成此任务。就像首先从主题表中提取所有主题名称，然后创建一个临时 View 并填充它。

如果您能提供任何帮助，我将不胜感激。非常感谢

Answer 1

我还没有测试过这个，而且我对 MySQL 的经验有限，但我希望下面的内容是您所追求的。它使用GROUP_CONCAT函数动态创建SELECT语句，然后执行它(这是我不确定在MySQL中执行此操作的方法)。

SET @SQL = (
SELECT  CONCAT('SELECT Email,', GROUP_CONCAT(SelectText), ' FROM Topic_User tu INNER JOIN Users u ON u.User_ID = tu.User_ID GROUP BY Email')
FROM    (   SELECT  CONCAT(' MAX(CASE WHEN Topic_ID = ', Topic_ID, ' THEN tu.Date_Created END) AS `', Topic_Name, '`') AS SelectText
            FROM    Topic
        )  AS d);

PREPARE stmt FROM @SQL;
EXECUTE stmt

当然，如果您的主题变化不频繁，您可以使用:

SELECT  Email,
        MAX(CASE WHEN Topic_ID = 1 THEN tu.Date_Created END) AS ABC,
        MAX(CASE WHEN Topic_ID = 2 THEN tu.Date_Created END) AS DEF,
        MAX(CASE WHEN Topic_ID = 3 THEN tu.Date_Created END) AS GHI,
        MAX(CASE WHEN Topic_ID = 4 THEN tu.Date_Created END) AS JKL,
        MAX(CASE WHEN Topic_ID = 5 THEN tu.Date_Created END) AS XYZ
FROM    Topic_User tu
        INNER JOIN users u
            ON u.User_ID = tu.User_ID
GROUP BY Email

并在每次添加新主题时更改查询(这是上述过程生成的查询)。