SQL - 不同类别的列

Question

我是 SQL 新手。我有一个包含不同考试数据的数据库，例如：

Student Test Grade
--------------------
St1    T1   A
St2    T1   B
St3    T1   B
St1    T2   B
St2    T2   B
St3    T2   A
St1    T3   A
St2    T3   C
St3    T3   B

然后，我想使用测试（T1、T2 和 T3）作为列来打印报告：

Student  T1   T2   T3
----------------------
St1      A    B    A
St2      B    B    C
St3      B    A    B

我尝试了不同的事情，但我陷入了如何生成这样的问题打印输出。任何帮助表示赞赏！

Answer 1

使用：

  SELECT t.student,
         MAX(CASE WHEN t.test = 'T1' THEN t.grade END) AS T1,
         MAX(CASE WHEN t.test = 'T2' THEN t.grade END) AS T2,
         MAX(CASE WHEN t.test = 'T3' THEN t.grade END) AS T3
    FROM TABLE t
GROUP BY t.student

Answer 2

不久前我问了一个类似的问题。您需要类似于数据透视表的东西，但是，这在 SQLite 中不可用（据我所知）。

Answer 3

有几种方法可以做到这一点，这两种方法（在纯 SQL 中而不是生成 SQL 命令的代码中）都需要已知并固定列数。最直接的实现是：

SELECT eg.Student,
(SELECT Grade from ExamGrade eg1 WHERE eg1.Student = eg.Student AND Test = 'T1') AS T1
(SELECT Grade from ExamGrade eg2 WHERE eg2.Student = eg.Student AND Test = 'T2') AS T2
(SELECT Grade from ExamGrade eg3 WHERE eg3.Student = eg.Student AND Test = 'T3') AS T3
FROM ExamGrade eg

这几乎可以在包括 SQLite 在内的任何环境中工作，并且可以使用标量值函数 GetTest() 使其变得更加优雅，该函数将获取学生和测试编号并返回成绩。然而，无论如何，这既不是高效的，也不是无法改变的。它将在表中查询 N 次 N 次测试，如果您添加第 4 个测试，则必须更改此查询以将其包含在报告中。

如果 Student 和 Test 的组合是唯一的，并且您正在使用具有 Pivot 功能的数据库（显然 SQLite 没有），则可以将 Pivot 查询与任何聚合器（MAX/MIN/AVG/具有单个值的集合的总和就是该值）。 MSS2005 中的以下工作：

SELECT Student, T1, T2, T3
FROM (Select Student, Test, Grade FROM ExamGrade) As SourceQuery
PIVOT (MAX(Grade) FOR Test IN (T1, T2, T3)) AS PivotTable

这将具有更高的性能，并且更加优雅。 AFAIK 仍然无法动态确定列列表，但如果您从应用程序代码进行此查询，或者使用 MS SQL Server 中的 sp_executesql 内置存储过程从另一个存储过程生成查询，则生成它们很简单功能。

Answer 4

我相信，如果您要扩展此系统以包含更多信息，那么您可以从重新设计数据库中受益，我会像这样构建它：

表名称 = 粗体

列名称 = 斜体

学生：

• SID（主键）
• 有关学生

测试的其他信息：

• TID（主键） )
• 有关测试的其他信息

测试成绩

• GID（主键）
• TID（外键）
• SID（外键） )
• 成绩

这种结构基于一种称为数据库规范化的思想（如果你用谷歌搜索，你会得到很多信息）。我将在下面给您一个部分总结，但是如果您要执行大量 SQL，您应该自己阅读：

首先要知道的是，主键只是一个唯一标识符，它通常不是 SQL 的一部分。信息（但是，由于它是唯一的，每个数据的主键必须具有不同的值），并且外键是一种使用被引用者的主键从一个表中的行引用另一个表中的行的方法：例如这里，每个年级的外键 SID 根据主键 SID 引用单个学生。

例如，学生 1 的 SID 为 1，并且他的所有测试的 SID 列中均为 1。学生 2、3、4 等也同样。

规范化的基本思想是，所有唯一数据仅存储一次，然后在使用它的其他地方引用（如果您看一下示例中键的结构，所有学生信息都存储在一个表中，并且然后在他们的测试成绩中引用，而不是在每个成绩中重复）。

要从这些表中检索您想要的内容，我将使用它（用 PHP 编写）：

$sql = 'SELECT * FROM Tests ORDER BY TID';
$tempresult = mysql_query($sql);
while($temprow = mysql_fetch_array($tempresult)){
    echo $temprow['TID'];
}

$sql = 'SELECT * FROM Students';
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
   echo '\n'.$row['SID'];
   $sql = 'SELECT * FROM Grades WHERE SID='.$row['SID'].' ORDER BY TID';
   $result2 = mysql_query($sql);
   while($row2 = mysql_fetch_array($result2)){
      echo ' '.$rows['Grade'];
   }
}

您可以在 echo 语句中为其添加格式，还可以打印您选择添加的任何额外信息。如果您有任何疑问，请询问他们。

编辑：我已经阅读了其他人，并同意他们的方法很可能是优越的，我唯一不确定的是数据透视表是否可以扩展以处理不同数量的测试，如果它可以（或者你不需要），那么我建议他们的方法，否则我觉得这可能在你的应用程序中占有一席之地。

Answer 5

我想为 @OMG_Ponies 答案添加一些解释，因为它对于 SQL 无超级用户（比如我自己）可能很有用

让我们创建一个示例表并添加虚拟数据：

CREATE TABLE t (
   t_ID integer primary key autoincrement not null,
   student integer,
   test text,
   grade text
);

INSERT INTO t 
   (student, test, grade)
VALUES
   ('St1', 'T1', 'A'),
   ('St2', 'T1', 'B'),
   ('St3', 'T1', 'B'),
   ('St1', 'T2', 'B'),
   ('St2', 'T2', 'B'),
   ('St3', 'T2', 'A'),
   ('St1', 'T3', 'A'),
   ('St2', 'T3', 'C'),
   ('St3', 'T3', 'B');

所以我们有以下内容：

t_ID student test grade
-------------------------
 1   St1     T1     A
 2   St2     T1     B
 3   St3     T1     B
 4   St1     T2     B
 5   St2     T2     B
 6   St3     T2     A
 7   St1     T3     A
 8   St2     T3     C
 9   St3     T3     B

使用语句 case when ... then ... end 可以获得所需的列

SELECT 
   t_ID,
   student,
   (case when t.test = 'T1' then t.grade end) as T1,
   (case when t.test = 'T2' then t.grade end) as T2,
   (case when t.test = 'T3' then t.grade end) as T3
FROM t
   order by student

结果

t_ID student  T1    T2     T3
----------------------------------
1    St1      A    NULL  NULL       
4    St1     NULL   B    NULL   
7    St1     NULL  NULL   A
2    St2      B    NULL  NULL   
5    St2     NULL   B    NULL   
8    St2     NULL  NULL   C
3    St3      B    NULL  NULL   
6    St3     NULL   A    NULL   
9    St3     NULL  NULL   B 

但是，我们看到有必要按字段“student”对结果进行分组。当我们分组时，我们必须指定一个聚合函数来指定在有多于一行具有相同“student”值的情况下要保留哪个值。在这种情况下，我们使用“max”函数来丢弃空值。

SELECT 
   t_ID,
   student,
   max(case when t.test = 'T1' then t.grade end) as T1,
   max(case when t.test = 'T2' then t.grade end) as T2,
   max(case when t.test = 'T3' then t.grade end) as T3
FROM t
GROUP BY student
ORDER BY student

结果

t_ID student  T1   T2   T3
-----------------------------
7    St1      A    B     A      
8    St2      B    B     C  
9    St3      B    A     B

最后说明。由于我们还没有按 t_ID 进行分组，也没有为其指定聚合函数，因此您应该假设每一行的 t_ID 值是每组的随机值。对此要小心。

Answer 6

试试这个

SELECT Student, MAX(CASE WHEN Test = 'T1' THEN Grade END) AS T1,
   MAX(CASE WHEN Test = 'T2' THEN Grade END) AS T2,
   MAX(CASE WHEN Test = 'T3' THEN Grade END) AS T3 FROM tablename  GROUP BY Student

使用您的表名称而不是“tablename”。