带有日期和和的MySQL动态轴到C# GridView

Question

我从MySQL数据库获取了以下数据：

+------------+------------+----------------+
| competency | totalscore | date_evaluated |
+------------+------------+----------------+
|     A      |      0     |     null       |
|     B      |      0     |     null       |
|     C      |      0     |     null       |
|     D      |      0     |     null       |
|     E      |      5     |   2018-03-01   |
|     F      |      4     |   2018-03-01   |
|     E      |      4     |   2018-03-02   |
|     F      |      3     |   2018-03-02   |
+------------------------------------------+

从两个单独的表中检索到

+-----------------+
|  competencies   | 
+-----------------+
|  competency_ID  |
|  competency     |
+-----------------+

+-----------------+
|  scoring_self   | 
+-----------------+
|  student_ID     |
|  competency_ID  |
|  score          |
|  date_evaluated |
+-----------------+

我想以以下形式在.aspx页面上显示：

+----------------+-------+-------+-------+-------+-------+--------+
| Date Evaluated |   A   |   B   |   C   |   D   |   E   |   F    |
+----------------+-------+-------+-------+-------+-------+--------+
| 2018-03-01     |   0   |   0   |   0   |   0   |   5   |   4    |
| 2018-03-02     |   0   |   0   |   0   |   0   |   4   |   3    |
+-----------------------------------------------------------------+

如果用户尚未尝试，则得分为0，且评估日期为空。我尝试创建一个动态枢轴表，因为能力的数量可以根据主题的不同而变化，使用以下准备好的语句。

SET @mysql = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT('(CASE WHEN score IS NOT NULL THEN score ELSE 0 END) AS `',
competency, '`')) INTO @mysql
FROM (SELECT competency, scoring_self.score FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID
ORDER BY competency) AS sub;


SET @mysql2= CONCAT('SELECT DISTINCT(DATE(ss.date_evaluated)) AS `Date
Evaluated`, ' , @mysql, 
'FROM competency c INNER JOIN scoring_self ss
ON c.competency_ID = ss.competency_ID
GROUP BY ss.date_evaluated;');

PREPARE stmt FROM @mysql2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

但是，我只设法以以下形式获得了它：

+----------------+-------+-------+-------+-------+-------+--------+
| Date Evaluated |   A   |   B   |   C   |   D   |   E   |   F    |
+----------------+-------+-------+-------+-------+-------+--------+
| 2018-03-01     |   5   |   5   |   5   |   5   |   5   |   5    |
| 2018-03-02     |   4   |   4   |   4   |   4   |   4   |   4    |
+-----------------------------------------------------------------+

这似乎只是从第一次约会开始的第一组分数，我不太确定到底出了什么问题。任何帮助都将不胜感激！

另外，我不太确定在准备好的语句中放置where子句的位置，以便只检索单个学生的分数。

编辑：我尝试了jQuery建议的更改，并做了一些调整：

SELECT
    GROUP_CONCAT(DISTINCT
                     CONCAT('(CASE WHEN score IS NOT NULL THEN score ELSE 0 END) AS `', competency, '`')) INTO @mysql
FROM 
    (SELECT competency 
     FROM competency 
     LEFT JOIN scoring_self ON competency.competency_ID = scoring_self.competency_ID 
                            AND scoring_self.student_ID = <redacted>
     GROUP BY DATE(scoring_self.date_evaluated), competency) AS sub;

这将返回以下输出：

+----------------+-------+-------+-------+-------+-------+--------+
| Date Evaluated |   A   |   B   |   C   |   D   |   E   |   F    |
+----------------+-------+-------+-------+-------+-------+--------+
| 2018-03-01     |   5   |   5   |   5   |   5   |   5   |   5    |
| 2018-03-01     |   4   |   4   |   4   |   4   |   4   |   4    |
| 2018-03-02     |   4   |   4   |   4   |   4   |   4   |   4    |
| 2018-03-02     |   3   |   3   |   3   |   3   |   3   |   3    |
+-----------------------------------------------------------------+

似乎分数不是映射到列，他们应该是出于我无法理解的原因。

编辑2：

解决了jQuery的礼貌问题

最后准备好的语句应该如下所示

SET @mysql_dynamic = (
    SELECT
        GROUP_CONCAT( DISTINCT
            CONCAT(
                 'MAX(CASE WHEN competency = ''',
      competency,
      ''' AND competency IS NOT NULL THEN score ELSE 0 END) AS `',
      competency,
      '`'
            )
        )
    FROM (SELECT competency, scoring_self.score, student_ID FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID 
ORDER BY competency) AS sub
);

SET @mysql = CONCAT('SELECT ss.date_evaluated, ', @mysql_dynamic, ' 
FROM (SELECT date_evaluated, competency, scoring_self.score, scoring_self.student_ID FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID 
ORDER BY competency) AS ss WHERE ss.date_evaluated IS NOT NULL AND ss.student_ID = \'',<redacted>,'\' GROUP BY date_evaluated DESC');

PREPARE stmt FROM @mysql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

上述内容提供了学生ID可过滤的预期结果。

Answer 1

我想我们已经接近下面的代码了

这将给您希望的结果：

SET @sql_dynamic = (
    SELECT
        GROUP_CONCAT( DISTINCT
            CONCAT(
                 'max(case when competency = ''',
      competency,
      ''' and competency is not null then score end) AS `',
      competency,
      '`'
            )
        )
    FROM (SELECT competency, scoring_self.score FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID
ORDER BY competency) AS sub
);

SET @sql = CONCAT('SELECT ss.date_evaluated,', 
              @sql_dynamic, ' 
           FROM (SELECT date_evaluated, competency, scoring_self.score FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID
ORDER BY competency) AS ss WHERE ss.date_evaluated is not null
           GROUP BY date_evaluated DESC'
       );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

在该代码上，分数现在被正确地映射到能力。

更新：

找到了罪魁祸首，正是因为WHEN score IS NOT NULL THEN score ELSE 0，尝试删除第一段代码上的IS NOT NULL和ELSE 0，这应该与您的代码正确地映射列。但是尝试先查看我的更新并测试运行。

更新：

这是你的代码，我稍微修改了一下：

SET @mysql = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
                 'max(case when competency = ''',
      competency,
      ''' and competency is not null then score else 0 end) AS `',
      competency,
      '`'
            )
) INTO @mysql
FROM (SELECT competency, scoring_self.score FROM competency LEFT JOIN
scoring_self ON competency.competency_ID = scoring_self.competency_ID
ORDER BY competency) AS sub;


SET @mysql2= CONCAT('SELECT DISTINCT(DATE(ss.date_evaluated)) AS `Date
Evaluated`, ' , @mysql, 
'FROM competency c INNER JOIN scoring_self ss
ON c.competency_ID = ss.competency_ID
GROUP BY ss.date_evaluated;');
PREPARE stmt FROM @mysql2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;