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

我尝试了不同的方法，但在如何生成这样的打印输出方面遇到了困难。感激任何帮助！

- Michel Mesquita

---

使用“代码”按钮进行格式化，可以使用固定宽度字体，帮助这些表格排列整齐并更易读。 - ford

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

- OMG Ponies

---

嗨， 你太棒了！非常感谢！它完美地解决了问题！ 干杯！ -麦克 - Michel Mesquita

Answer 2

我希望能够补充一下@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 

然而，我们发现有必要按“学生”字段对结果进行分组。在分组时，我们必须指定聚合函数来确定在具有相同“学生”值的多行情况下保留哪个值。在这种情况下，我们使用“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值是每个组中随机的一个值。请注意这一点。

- pablo_worker

Answer 3

我相信如果你打算扩展这个系统以包含更多信息，那么重新设计数据库会对你有所帮助。我会按照以下方式构建它：

表名 = 粗体

列名 = 斜体

学生：

• SID（主键）
• 关于学生的其他信息

考试：

• TID（主键）
• 关于考试的其他信息

考试成绩

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

这种结构基于一种叫做数据库规范化的思想（如果你搜索一下，会得到很多相关信息）。我在下面给出了一个部分摘要，但如果你打算进行大量的SQL操作，你应该自己阅读相关资料：

首先要知道的是，主键只是一个唯一标识符，通常不是信息的一部分（然而，由于它是唯一的，每个数据必须具有不同的主键值），而外键是一种引用另一个表中的行的方法，使用被引用的主键：例如，在这里，每个成绩中的外键 SID 引用了一个单独的学生，基于他们的主键 SID。

例如，学生一有 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语句中添加格式，并打印出您选择添加的任何额外信息。 如果您有任何问题，请随时提问。

编辑：我已经阅读了其他人的内容，并同意他们的方法很可能更优秀，唯一不确定的是数据透视表是否可以扩展以处理不同数量的测试，如果可以（或者您不需要），那么我建议使用他们的方法，否则我认为这可能在您的应用程序中有一定的作用。

- lemiant

Answer 4

有几种方法可以做到这一点，两种方法都需要知道和固定列的数量（在纯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平方次，如果您添加第四个测试，则必须更改此查询以将其包含在报告中。

如果学生和测试的组合是唯一的，并且您正在使用具有Pivot功能的数据库（显然SQLite没有），则可以使用Pivot查询和任何聚合器（单个值集合的MAX/MIN/AVG/SUM是该值）。以下内容适用于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

这种方法将更加高效，且更加优美。就我所知，目前仍然无法动态确定列列表，但如果您从应用程序代码进行此查询或使用 MS SQL Server 中的 sp_executesql 内置存储过程从另一个存储过程或函数生成查询，则可以轻松生成它们。

- KeithS

Answer 5

我之前问过一个类似的问题。你需要像透视表一样的东西，但是SQLite中好像没有这个功能。

- Topher Fangio

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"。

- KMK