SQL - столбцы для разных категорий

Есть несколько способов сделать это, оба из которых (в чистом 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/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

Это будет более производительно и намного элегантнее. Списки столбцов по-прежнему не могут быть динамически определены AFAIK, но их легко сгенерировать, если вы делаете этот запрос из кода приложения или используете встроенную хранимую процедуру sp_executesql в MS SQL Server для генерации запроса из другой хранимой процедуры или функция.

Я считаю, что если вы собираетесь расширить эту систему, чтобы включить в нее больше информации, вам может быть полезно переработать свою базу данных, я бы построил ее следующим образом:

Имя таблицы = Жирный

Имя столбца = курсивом

Студенты:

• SID (первичный ключ)
• Другая информация о студенте

Тесты:

• TID (первичный ключ )
• Прочая информация о тесте

Оценки за тест

• GID (первичный ключ)
• TID (внешний ключ)
• SID (внешний ключ)
• Оценка

Эта структура основана на идее, называемой нормализацией базы данных (вы получите много информации, если погуглите). Ниже я дам вам частичное резюме, но если вы собираетесь много работать с SQL, вам следует прочитать об этом самостоятельно:

Первое, что нужно знать, это то, что первичный ключ — это просто уникальный идентификатор, а не обычно часть информации (однако, поскольку она уникальна, каждый элемент данных должен иметь различное значение для своего первичного ключа), а внешний ключ — это способ сослаться на строку в одной таблице из строки в другой таблице, используя первичный ключ ссылки : например, здесь внешний ключ SID в каждом классе ссылается на одного учащегося на основе его первичного ключа SID.

напр. У первого учащегося SID 1, и все его тесты имеют 1 в столбце SID. то же самое для ученика 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'];
   }
}

Вы можете добавить форматирование к этому в операторах эха, а также распечатать любую дополнительную информацию, которую вы решите добавить. Если у вас есть какие-либо вопросы, пожалуйста, задайте их.

РЕДАКТИРОВАТЬ: Я прочитал другие и согласен с тем, что их метод, по всей вероятности, лучше, единственное, в чем я не уверен, так это в том, могут ли сводные таблицы расширяться для обработки различного количества тестов, если это может (или вам не нужно), тогда я предлагаю их метод, иначе я чувствую, что это может иметь место в вашем приложении.