Как написать SQL ниже, чтобы отобразить все детали
Я не знаю, правильно ли я делаю метод ниже при использовании sql, но здесь это идет.
Я хочу отобразить все детали экзамена (подробности экзамена (также называемые сессией), вопросы на экзамене, ответы, штрафы, изображения, видео и т. Д.) Но способ, которым я пытаюсь это сделать, - это один большой запрос, но он не работает, как нет строки отображаются
Ниже показано, что я хочу показать для одного экзамена (и только одного экзамена):
- Детали экзамена
- Все вопросы на экзамене
- Все ответы на каждый вопрос и оценки каждого ответа стоит
- Штраф (посмотреть, если включен или нет)
- Наказания за неправильные ответы
- Изображения в вопросе
- Видео в вопросах
- Аудио в вопросах
- Детали модуля
- Ученик
Ниже покажите поля, которые каждый раздел требует от базы данных:
Детали экзамена
SessionId, SessionName, SessionDuration, TotalMarks, SessionWeightВсе вопросы на экзамене
QuestionId, QuestionNo, QuestionContent, NoofAnswers, QuestionMarks, OptionId, OptionType, ReplyId, ReplyTypeВсе ответы на каждый вопрос и оценки каждого ответа стоит
AnswerId, Answer, AnswerMarks
- Штраф (посмотреть, если включен или нет)
PenaltyEnabled
- Штрафные ответы
PenaltyAnswerId, PenaltyAnswer, PenaltyMarks
- Изображения в вопросе
ImageId, ImageFile
- Видео в вопросах
VideoId VideoFile
- Аудио в вопросах
AudioId, AudioFile
- Детали модуля
ModuleId, ModuleNo, ModuleName
- Ученик
StudentId
У меня вопрос, как должен быть написан код SQL, чтобы все эти данные отображались? Мне нужен только один большой запрос или много маленьких запросов? Моя попытка в нижней части вопроса, но ниже таблицы базы данных, которые показывают детали, касающиеся одного экзамена и все его детали.
ТАБЛИЦЫ:
Ученик
StudentId (PK) StudentForename StudentSurname
1 James Parker
Student_Session
SessionId (FK) StudentId (FK)
1 1
сессия
SessionId (PK) SessionName SessionDuration TotalMarks SessionWeight ModuleId (FK)
1 AAA 01:00:00 30 20 1
модуль
ModuleId (PK) ModuleNo ModuleName
1 CHI2513 ICT
Вопрос
SessionId FK) QuestionId (PK) QuestionNo QuestionContent NoofAnswers QuestionMarks OptionId (FK) ReplyId (FK)
1 4 1 Question 1 1 5 1 1
1 5 2 Question 2 1 3 2 1
1 6 3 Question 3 2 6 2 2
1 7 4 Question 4 3 7 5 2
1 8 5 Question 5 1 9 5 1
Ответ:
AnswerId (PK) Answer QuestionId (FK)
1 A 4
2 C 5
3 A 6
4 B 6
5 B 7
6 D 7
7 E 7
8 G 8
Индивидуальный ответ
IndividualId (PK) AnswerId (FK) AnswerMarks
1 1 3
2 2 5
3 3 3
4 4 3
5 5 2
6 6 2
7 7 3
8 8 9
неустойка
PenaltyId(PK) SessionId (FK) PenaltyEnalbed
1 1 1
PenaltyMarks
PenaltyAnswerId (PK) PenaltyAnswer PenaltyMarks QuestionId (FK)
1 B 1 4
2 C 1 4
3 A 1 5
4 B 1 5
5 D 1 5
6 C 2 6
7 D 2 6
8 A 1 7
9 C 1 7
10 F 1 7
11 G 1 7
12 A 0 8
13 B 0 8
14 C 1 8
15 D 1 8
16 E 1 8
17 F 0 8
Ответить
ReplyId (PK) ReplyType
1 Single
2 Multiple
Option_Table
OptionId (PK) OptionType
1 A-C
2 A-D
3 A-E
4 A-F
5 A-G
Образ
ImageId (PK) ImageFile
1 ImageFile/Tulips.png
2 ImageFile/Daisys.png
видео
VideoId (PK) VideoFile
1 VideoFile/Speech.png
аудио
AudioId (PK) AudioFile
1 AudioFile/Song.png
Image_Question
ImageQuestionId (PK) ImageId (FK) SessionId (FK) QuestionNo (FK)
1 1 1 2
Video_Question
VideoQuestionId (PK) VideoId (FK) SessionId (FK) QuestionNo (FK)
1 1 1 4
Audio_Question
AudioQuestionId (PK) AudioId (FK) SessionId (FK) QuestionNo (FK)
1 1 1 5
Ниже была моя неудачная попытка:
SELECT s.SessionId,
SessionName,
SessionDuration,
TotalMarks,
SessionWeight,
q.QuestionId,
q.QuestionNo,
QuestionContent,
QuestionMarks,
q.OptionId,
OptionType,
q.ReplyId,
ReplyType,
a.AnswerId,
Answer,
NoofAnswers,
AnswerMarks,
PenaltyEnabled,
PenaltyAnswerId,
PenaltyAnswer,
PenaltyMarks,
i.ImageId,
au.AudioId,
v.VideoId,
ImageFile,
AudioFile,
VideoFile,
s.ModuleId,
ModuleNo,
ModuleName,
ss.StudentId
FROM Student st
INNER JOIN Student_Session ss
ON st.StudentId = ss.StudentId
INNER JOIN Session s
ON ss.SessionId = s.SessionId
INNER JOIN Question q
ON s.SessionId = q.SessionId
INNER JOIN Answer a
ON q.QuestionId = a.AnswerId
INNER JOIN Individual_Answer ia
ON a.AnswerId = ia.AnswerId
LEFT JOIN Module m
ON s.ModuleId = m.ModuleId
LEFT JOIN Penalty p
ON q.SessionId = p.SessionId
LEFT JOIN Option_Table o
ON q.OptionId = o.OptionId
LEFT JOIN Reply r
ON q.ReplyId = r.ReplyId
LEFT JOIN Penalty_Marks pm
ON q.QuestionId = pm.QuestionId
LEFT JOIN Image_Question iq
ON q.QuestionId = iq.QuestionNo
INNER JOIN Image i
ON iq.ImageId = i.ImageId
LEFT JOIN Audio_Question aq
ON q.QuestionId = aq.QuestionNo
INNER JOIN Audio au
ON aq.AudioId = au.AudioId
LEFT JOIN Video_Question vq
ON q.QuestionId = vq.QuestionNo
INNER JOIN Video v
ON vq.VideoId = v.VideoId
WHERE s.SessionId = 1
ORDER BY q.QuestionId
ОБНОВИТЬ:
Сможет ли приведенный ниже код вставлять QuestionId в таблицу Image_Question после отправленных вопросов: (Это то, что я нашел в SO, но обновил до своего дизайна таблицы)
ImageQuestionToken
{
ImageToken (PK auto)
SessionId
}
Image{
ImageId
ImageFile
}
Image_Question
{
Image_QuestionId
ImageId FK references Image(ImageId)
QuestionId FK references Question(QuestionId)
}
Question
{
QuestionId (PK Auto)
QuestionNo
QuestionContent
....
}
TempImage
{
ImageToken FK references ImageQuestionToken(ImageToken)
ImageFile
}
И логика такова:
- Пользователь запрашивает форму вопроса. Сервер устанавливает токен и включает в ответ пользователя.
- Пользователь загружает картинку, включая токен. Изображение хранится во временной таблице.
- Шаг 2 повторяется n раз.
- Если пользователь отправляет вопрос со значением токена, запись помещается в таблицу вопросов и присваивается идентификатор. Все изображения в таблице TempImage, имеющие общий токен, вставляются в таблицу изображений с теперь известным QuestionId. Затем запись ImageQuestionToken удаляется, а каскад удаляет временные изображения в TempImage.
- Если пользователь не отправит вопрос, файлы будут удалены, а запись в ImageQuestionToken удалена.
4 ответа
В этих таблицах есть проблема с вашими внешними ключами: Image_Question, Audio_Question и Video_Question.
Вы используете столбец QuestionNo, который не проиндексирован таблицей Question. Я бы предложил заменить столбцы на QuestionId и использовать их значения.
Image_Question
ImageQuestionId (PK) ImageId (FK) SessionId (FK) QuestionNo(FK)
1 1 1 2
Video_Question
VideoQuestionId (PK) VideoId (FK) SessionId (FK) QuestionNo(FK)
1 1 1 4
Audio_Question
AudioQuestionId (PK) AudioId (FK) SessionId (FK) QuestionNo(FK)
1 1 1 5
и ваше заявление должно выглядеть так:
SELECT s.SessionId,
SessionName,
SessionDuration,
TotalMarks,
SessionWeight,
q.QuestionId,
q.QuestionNo,
QuestionContent,
QuestionMarks,
q.OptionId,
OptionType,
q.ReplyId,
ReplyType,
a.AnswerId,
Answer,
NoofAnswers,
AnswerMarks,
PenaltyEnabled,
PenaltyAnswerId,
PenaltyAnswer,
PenaltyMarks,
i.ImageId,
au.AudioId,
v.VideoId,
ImageFile,
AudioFile,
VideoFile,
s.ModuleId,
ModuleNo,
ModuleName,
ss.StudentId
FROM Student st
INNER JOIN Student_Session ss
ON st.StudentId = ss.StudentId
INNER JOIN Session s
ON ss.SessionId = s.SessionId
INNER JOIN Question q
ON s.SessionId = q.SessionId
INNER JOIN Answer a
ON q.QuestionId = a.AnswerId
INNER JOIN Individual_Answer ia
ON a.AnswerId = ia.AnswerId
LEFT JOIN Module m
ON s.ModuleId = m.ModuleId
LEFT JOIN Penalty p
ON q.SessionId = p.SessionId
LEFT JOIN Option_Table o
ON q.OptionId = o.OptionId
LEFT JOIN Reply r
ON q.ReplyId = r.ReplyId
LEFT JOIN Penalty_Marks pm
ON q.QuestionId = pm.QuestionId
LEFT JOIN Image_Question iq
ON q.QuestionNo= iq.QuestionNo
LEFT JOIN Image i
ON iq.ImageId = i.ImageId
LEFT JOIN Audio_Question aq
ON q.QuestionNo= aq.QuestionNo
LEFT JOIN Audio au
ON aq.AudioId = au.AudioId
LEFT JOIN Video_Question vq
ON q.QuestionNo= vq.QuestionNo
LEFT JOIN Video v
ON vq.VideoId = v.VideoId
WHERE s.SessionId = 1
ORDER BY q.QuestionId
Обновление: вы можете использовать столбец QuestionNo, в конце концов, вы можете просто добавить индекс, чтобы вам не пришлось переделывать свои коды. хехехе
Я предлагаю вам добавить это:
ALTER TABLE `your_schema`.`Question`
ADD INDEX `your_idx` (`QuestionNo` ASC);
и вместо них:
LEFT JOIN Image_Question iq
ON q.QuestionId = iq.QuestionId -- from iq.QuestionNo
LEFT JOIN Image i
ON iq.ImageId = i.ImageId
LEFT JOIN Audio_Question aq
ON q.QuestionId = aq.QuestionId -- from aq.QuestionNo
LEFT JOIN Audio au
ON aq.AudioId = au.AudioId
LEFT JOIN Video_Question vq
ON q.QuestionId = vq.QuestionId -- from vq.QuestionNo
LEFT JOIN Video v
ON vq.VideoId = v.VideoId
Вы используете это:
LEFT JOIN Image_Question iq
ON q.QuestionNo= iq.QuestionNo
LEFT JOIN Image i
ON iq.ImageId = i.ImageId
LEFT JOIN Audio_Question aq
ON q.QuestionNo= aq.QuestionNo
LEFT JOIN Audio au
ON aq.AudioId = au.AudioId
LEFT JOIN Video_Question vq
ON q.QuestionNo= vq.QuestionNo
LEFT JOIN Video v
ON vq.VideoId = v.VideoId
Пока я не на 100% уверен в том, какой именно результат вы хотите. На основе вашего существующего запроса проблема заключается в том, что вы используете INNER JOIN на некоторых из таблиц, когда вы должны использовать LEFT JOIN,
В вашем существующем запросе вы используете следующее:
LEFT JOIN Image_Question iq
ON q.QuestionId = iq.QuestionNo
INNER JOIN Image i
ON iq.ImageId = i.ImageId
LEFT JOIN Audio_Question aq
ON q.QuestionId = aq.QuestionNo
INNER JOIN Audio au
ON aq.AudioId = au.AudioId
LEFT JOIN Video_Question vq
ON q.QuestionId = vq.QuestionNo
INNER JOIN Video v
ON vq.VideoId = v.VideoId
Проблема в том, что INNER JOIN проверяет соответствие записей во всех полях, но у вас может не быть записи, существующей в image, audio или же video таблицы, так что ничего не возвращается.
Исходя из ваших данных ваш запрос должен быть похож на это:
select st.studentid,
s.sessionid,
s.sessionname,
s.sessionduration,
s.totalmarks,
s.sessionweight,
q.questionid,
q.questionno,
q.questioncontent,
q.noofanswers,
q.questionmarks,
q.optionid,
ot.optiontype,
q.replyid,
r.replytype,
a.answerid,
a.answer,
ia.answermarks,
p.penaltyenabled,
pm.penaltyanswerid,
pm.penaltyanswer,
pm.penaltymarks,
i.imageid,
i.imagefile,
v.videoid,
v.videofile,
ad.audioid,
ad.audiofile,
m.moduleid,
m.moduleno,
m.modulename
from Student st
inner join Student_Session ss
on st.studentid = ss.studentid
inner join session s
on ss.sessionid = s.sessionid
inner join question q
on s.sessionid = q.sessionid
inner join answer a
on q.questionid = a.questionid
inner join Individual_Answer ia
on a.answerid = ia.answerid
left join Option_Table ot
on q.optionid = ot.optionid
left join reply r
on q.replyid = r.replyid
left join module m
on s.moduleid = m.moduleid
left join Penalty p
on s.sessionid = p.sessionid
left join penalty_marks pm
on q.questionid = pm.questionid
left join image_question iq -- note I am joining on both session and question
on s.sessionid = iq.sessionid
and q.questionid = iq.questionno
left join image i -- this should be a left join not inner join
on iq.imageid = i.imageid
left join video_question vq -- note I am joining on both session and question
on s.sessionid = vq.sessionid
and q.questionid = vq.questionno
left join video v -- this should be a left join not inner join
on vq.videoid = v.videoid
left join audio_question aq -- note I am joining on both session and question
on s.sessionid = aq.sessionid
and q.questionid = aq.questionno
left join audio ad -- this should be a left join not inner join
on aq.audioid = ad.audioid
where s.SessionId = 1
order by q.QuestionId
Смотрите SQL Fiddle с демонстрацией.
Это возвращает все данные, которые вы запросили выше. Образец:
| STUDENTID | SESSIONID | SESSIONNAME | SESSIONDURATION | TOTALMARKS | SESSIONWEIGHT | QUESTIONID | QUESTIONNO | QUESTIONCONTENT | NOOFANSWERS | QUESTIONMARKS | OPTIONID | OPTIONTYPE | REPLYID | REPLYTYPE | ANSWERID | ANSWER | ANSWERMARKS | PENALTYENABLED | PENALTYANSWERID | PENALTYANSWER | PENALTYMARKS | IMAGEID | IMAGEFILE | VIDEOID | VIDEOFILE | AUDIOID | AUDIOFILE | MODULEID | MODULENO | MODULENAME |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 4 | 1 | Question 1 | 1 | 5 | 1 | A-C | 1 | Single | 1 | A | 3 | 1 | 1 | B | 1 | (null) | (null) | 1 | VideoFile/Speech.png | (null) | (null) | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 4 | 1 | Question 1 | 1 | 5 | 1 | A-C | 1 | Single | 1 | A | 3 | 1 | 2 | C | 1 | (null) | (null) | 1 | VideoFile/Speech.png | (null) | (null) | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 5 | 2 | Question 2 | 1 | 3 | 2 | A-D | 1 | Single | 2 | C | 5 | 1 | 3 | A | 1 | (null) | (null) | (null) | (null) | 1 | AudioFile/Song.png | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 5 | 2 | Question 2 | 1 | 3 | 2 | A-D | 1 | Single | 2 | C | 5 | 1 | 5 | D | 1 | (null) | (null) | (null) | (null) | 1 | AudioFile/Song.png | 1 | CHI2513 | ICT |
| 1 | 1 | AAA | 01:00:00 | 30 | 20 | 5 | 2 | Question 2 | 1 | 3 | 2 | A-D | 1 | Single | 2 | C | 5 | 1 | 4 | B | 1 | (null) | (null) | (null) | (null) | 1 | AudioFile/Song.png | 1 | CHI2513 | ICT |
Ну, попробуйте это небольшими сменами: я имею в виду следующее:- Выполните запрос по частям, сначала удалите несколько объединений (удалите большинство из них), и добавьте их одно за другим, я делаю то же самое для своих огромных запросов, чтобы отслеживать, где является реальной проблемой, или какие условия делают основные различия для получения результирующих строк. просто идея. Ура!
И я думаю
LEFT JOIN Image_Question iq
ON q.QuestionId = iq.QuestionNo
даст вам нулевые значения
Поскольку q.QuestionId начинается с формы 4, а iq.QuestionNo равен 2
INNER JOIN Image i
ON iq.ImageId = i.ImageId
И все значения в полях Iq, полученные из левых внешних объединений, равны нулю. Поэтому любое внутреннее объединение на них обязательно даст вам нулевые результаты.
Ключевая проблема: часто строки не возвращаются с использованием внутреннего соединения внешней объединенной таблицы
Я запросил, используя синтаксис Microsoft SQL. Надеюсь, поможет. Посмотрите мои предупреждения внизу и проигнорируйте оператор процедуры create, если хотите, вместо этого просто объявите и установите @SessionID равным 1 в верхней части.
create procedure GetStuff(
@SessionID as integer
) as
declare @StudentID int -- you may need to use a different data type
select @StudentID = StudentID from Student_Session where SessionID = @SessionID
-- now we can at least remove the sessions table from the join list.
SELECT s.SessionId,
SessionName,
SessionDuration,
TotalMarks,
SessionWeight,
q.QuestionId,
q.QuestionNo,
QuestionContent,
QuestionMarks,
q.OptionId,
OptionType,
q.ReplyId,
ReplyType,
a.AnswerId,
Answer,
NoofAnswers,
AnswerMarks,
PenaltyEnabled,
PenaltyAnswerId,
PenaltyAnswer,
PenaltyMarks,
i.ImageId,
au.AudioId,
v.VideoId,
ImageFile,
AudioFile,
VideoFile,
s.ModuleId,
ModuleNo,
ModuleName,
ss.StudentId
FROM Student st
INNER JOIN Student_Session ss
ON st.StudentId = ss.StudentId
INNER JOIN Session s
ON ss.SessionId = s.SessionId
INNER JOIN Question q
ON s.SessionId = q.SessionId
INNER JOIN Answer a
ON q.QuestionId = a.AnswerId
INNER JOIN Individual_Answer ia
ON a.AnswerId = ia.AnswerId
LEFT outer JOIN Module m
ON s.ModuleId = m.ModuleId
LEFT outer JOIN Penalty p
ON q.SessionId = p.SessionId
LEFT outer JOIN Option_Table o
ON q.OptionId = o.OptionId
LEFT outer JOIN Reply r
ON q.ReplyId = r.ReplyId
LEFT outer JOIN Penalty_Marks pm
ON q.QuestionId = pm.QuestionId
-- you can't inner join off of an outer joined table, or you'll get no rows for the entire query. I've nested the query, which is bad practice but... practical for getting this monster of a query working.
LEFT outer JOIN (select c3.ImageID, c3.ImageFile from Image_Question as a1 inner join Images as b1 on a1.QuestionNo = b1.QuestionNo inner join Image as c1 on a1.ImageID = c1.ImageID)
ON q.QuestionID = iq.QuestionID
-- you can't inner join off of an outer joined table, or you'll get no rows for the entire query. I've nested the query, which is bad practice but... practical for getting this monster of a query working.
LEFT outer JOIN (select b2.AudioID, b2.AudioFile from Audio_Question as a2 inner join Audio as b2 on a2.AudioID = b2.AudioID) as aq
ON q.QuestionId = aq.QuestionNo
-- you can't inner join off of an outer joined table, or you'll get no rows for the entire query. I've nested the query, which is bad practice but... practical for getting this monster of a query working.
left outer join (select b3.VideoID, b3.VideoFile from Video_Question as a3 inner join Video as b3 on a3.QuestionID = b3.QuestionID) as vq
WHERE
st.StudentID = @StudentID
and
s.SessionId = @SessionID
ORDER BY q.QuestionId
go
Предупреждения: вы действительно хотите разбить это на отдельные кусочки для отображения в вашем клиентском приложении. Например, для экзаменов и экзаменов требуется подробное отображение мастера / детализации, в противном случае вам придется подавлять повторяющиеся значения в выводе, отображаемом для пользователя. Например, ваш вывод будет выглядеть так, концептуально:
<Exam Detail Fields> <Exam Detail Fields>
first one unique What is the color of your DOG?
repeat What is the color of your CAT?
repeat What is the color of your CAR?
repeat What is the color of your HAT?
Обратите внимание на повторяющиеся значения слева, которые вы должны будете подавить.