Выбор SQL: двумерный выбор с переменным количеством столбцов

CREATE TABLE activities(activityid, name);
CREATE TABLE activity_scores(activityid, studentid, score);   
CREATE TABLE students (id, name);

Есть ли способ написать один запрос SELECT, который будет выдавать один результат для каждого ученика в этом формате?

studentid | studentname | activity1_score | activity2_score | activity3_score [...]

Сделать это с помощью нескольких запросов тривиально:

for each studentid in "select id from students":
    print studentid
    for each activity in "select activityid from activities":
        select score from activity_scores where studentid = studentid
        print score

(псевдокод, я знаю, что это не так)

Конечно, есть способ создать такой результат с помощью одного запроса SELECT, верно?


sql select
person Community    schedule 14.06.2009    source источник
comment
Каковы ваши ограничения уникальности? Может ли один ученик участвовать более чем в одном мероприятии? Может ли один учащийся участвовать в одном и том же мероприятии более одного раза? Может ли одинокий ученик заниматься одним и тем же мероприятием более одного раза и получать одинаковые баллы более одного раза?   -  person John Saunders    schedule 14.06.2009
comment
Допустим, у каждого ученика будет только один балл за одно задание, и он должен иметь ровно один балл за каждое действие.   -  person    schedule 14.06.2009

Ответы (6)


arrow_upward
3
arrow_downward

MySQL, SQLite и, возможно, другие СУБД имеют что-то под названием GROUP_CONCAT,
, которое должно делать что-то вроде того, что вы хотите (не тестировалось - не знаю вашего условия соединения в таблице activity_scores):

SELECT   students.studentid
         , students.studentname
         , GROUP_CONCAT(activity_scores.score)

FROM     activity_scores 
         INNER JOIN activities 
         ON activities.activityid = activity_scores.activityid
         INNER JOIN students 
         ON students.studentid = activities.studentid

GROUP BY students.studentid 
         , students.studentname

но люди умнее меня можно сказать, что выполнение чего-то подобного сбивает с толку два разных шага, с которыми легче всего справиться, если оставить их по отдельности:

  • доступ к данным / сбор
  • представление данных
person mechanical_meat    schedule 14.06.2009

arrow_upward
2
arrow_downward

Вы в основном ищете сводную таблицу. Если вы просто хотите использовать чистый ANSI SQL, то сделать это невозможно. SQL генерирует наборы результатов только с предсказуемым числом столбцов (не считая select *).

Однако может быть способ сделать это, зависящий от конкретной технологии. Какой движок базы данных вы используете? SQL Server 2005 может создавать сводные таблицы.

person Jacob    schedule 14.06.2009

arrow_upward
1
arrow_downward

Такая форма набора результатов нарушает реляционную алгебру (на которой основан SQL). См. Сообщение об антипаттерне SQL № 2 Каковы наиболее распространенные SQL антипаттерны?

Вы должны выполнить этот запрос и отформатировать результаты на клиенте:

SELECT s.name, a.name, x.score
FROM Activity_Score as x
  JOIN Students s
  ON x.StudentID = s.StudentID
  JOIN Activity a
  ON x.ActivityID = a.ActivityID
ORDER BY s.name, a.name, x.score
person Amy B    schedule 14.06.2009

arrow_upward
1
arrow_downward

Один из наиболее часто задаваемых вопросов по SO.

Причина, по которой это не поддерживается в ANSI SQL, заключается в том, что набор результатов не определен четко - он будет иметь произвольно изменяющееся количество столбцов.

Однако, зная количество столбцов, можно сгенерировать код для этого, пример решения, который я всегда даю, генерирует код для SQL Server 2005 с использованием оператора PIVOT (список столбцов статический, поэтому это необходимо делать с помощью динамического SQL ) с помощью динамического SQL, а затем выполняет его.

person Cade Roux    schedule 14.06.2009
comment
+1 ANSI SQL заключается в том, что набор результатов не очень четко определен Фраза, которую я искал ... - person gbn; 14.06.2009

arrow_upward
1
arrow_downward

Если действия известны и могут быть указаны в запросе, то это относительно просто, хотя и немного многословно, при условии, что ваш диалект SQL поддерживает это. Например:

SELECT s.id, s.name,
  (SELECT score FROM activity_scores as JOIN activities a ON a.activityid = as.activityid WHERE studentid = s.id AND a.activityname = 'Basketball') basketball_score,
  (SELECT score FROM activity_scores as JOIN activities a ON a.activityid = as.activityid WHERE studentid = s.id AND a.activityname = 'Football') football_score,
  ...

Обычно это называется запросом кросс-таблицы. Если вы хотите сделать это динамически, это сложнее, и вам, вероятно, придется прибегнуть к коду или хранимой процедуре, поэтому это будет зависеть от вашей базы данных. Вот пример с использованием SQL Server < / а>.

person cletus    schedule 14.06.2009
comment
вы, вероятно, захотите назвать свой второй результат подзапроса football_score. - person akf; 14.06.2009

arrow_upward
0
arrow_downward

В SQL Server 2005/2008 вы можете попробовать вернуть действия / оценки для каждого студента в виде набора xml. Не идеально, но работает. Что-то типа:

SELECT s.name, 
(select a.name, x.score FROM FROM Activity_Score as x  
JOIN Activity a  ON x.ActivityID = a.ActivityID 
WHERE x.StudentID = s.StudentID FOR XML AUTO) Activities
FROM Students s
ORDER BY s.name
person Community    schedule 28.07.2009